DSXchange

Hi,

I am trying to view all the rows in my sequential file. however if a column contains a null value the row is not shown when i view the file. my particular file has around 30% nulls in a certain column and as result all the data is not being shown when i view. any suggestions as to how to correct this?? thanks in advance

the column in question is a date filed. when i specify the column type as a VarChar it shows all the rows. however when i specify the column type as a Date SQL type, it only shows non-null rows for the file.

Does your metadata specify this column as being nullable? I haven't noticed this before but if you have specified this column as being nullable then it does sound like a product bug.

hi

yes it is specified as a nullable column for the Date SQL Type and nullable for Varchar which returns all the rows.

thanks

It does look like a support issue. I'm not at a client where I can test this out but assume that they neglected to put a null handler for the date conversion in and that is why the rows are not being shown. Perhaps someone else here could do a simple test to see if they get the same problem as well.

are you using a delimiter to write the text file or using a fixed width file?

if you use null handling, you will be able to view the data in the seq file.

ya but if you are passing nulls to a fixed width file, i am not sure how the file will look like

Krazykoolrohit wrote:ya but if you are passing nulls to a fixed width file, i am not sure how the file will look like

when you are reading that sequential file, you can still tell DataStage how you represented nulls in your sequential file, and it can read your file accordingly,

for example lets say you replaced null fields by # characters, ( you can use padding as well, so fixed length field doesnot matter),
when you are reading it , just use the same char for nulls and you should be ok.

Original post marked as server

Server Jobs

The Format tab supplies to the reading process how null is represented in the file. By default this is empty, but should be replaced if null is represented in some other fashion (for example "<NULL>").

You can override this on a per-column basis by scrolling right in the Columns grid.

Parallel Jobs

In parallel jobs null handling can be more complex - you can provide either a null field value or a null field length property value (they are mutually exclusive). If the file is fixed width, then the null field value must be of precisely the same length as the field.

You get at these properties by choosing Edit Row from the Columns grid right-mouse menu.

Read more about null handling in the Parallel Job Developer's Guide page 3-42 and following.

thanks for all the suggestions guys. appreciate it.

I tried to do something similar but had to tweak here and there to view the data on the source file.
I had a column of datatype decimal containing nulls but when I tried doing view data, it was giving me an error. But when I changed the datatype to unkown I could view the data fine.
Is there a right way of doing this ?

Try with adding some DefaultNullableValue to that field as 0s.

When I try to go to the column extended properties and add a null value, it gives me an error saying that the null length must match fields fixed width.
(And the null value is mutually exclusive with the other options under NULLABLE)
And when I set the length it gives me another error saying that the actual length also has to be set.

Delimited or fixed width format?