Null handling in PX Sequential files

[ray.wurlod]

You can specify how null is represented as a stage property. However the OP's question was clearly about delimited records, so we're straying a little off topic. Lance's response could be read as suggesting that setting the environment variable also has an impact on delimited data, since that's what we were using in class last week. Lance?

[roy]

Hi,
I did a little test with a seq file I created having 1 row:
12,,12,12
reading it with column 2 as nullable and writing that file in direct link to another seq file only gave the nullable field with no null handling warning but the record was not rejected!
So what is different in your case???

p.s.
naturally I used a varchar(5) in the table definition for all fields.

[ukyrvd]

ROY,
did you check if DS is detecting that column as NULL? i.e. is IsNull or isNotNull catching that particular field?
---------------------
here is my problem/question

I am reading pipe delimited input file (eg rec below) with col1-2-3-4-5-6 as varchar

6595960093|||||text

Here if I say isNULL(col2) or isNULL(col3) .. DS is not catching them as NULL. On the other hand if (col2="") or if(col3="") are working fine.

Is this how its supposed to be working? or I have to set up some properties.

While writing to seq file after reading, I have setup NULL_FIELD value to be # so that all downstream jobs can recognize them as NULL. But as DS is not recognizing those values as NULLs, this approach is not working. Now I have added a transformer and used setNull() to set them to NULL.

Other than using transformer, is there any way to impliment if-then logic on incoming fields?

[legendkiller]

while reading from sequential file specify

null field value=''

I guess this will do

[dsxdev]

Hi,
Roy's test worked fine because the data type he used was varchar.
When a varchar field is read from a Sequential file the same record as below.

6595960093|||||text

is interpreted differently.

col2, col3,col4, col5 and col6 re varchar fields in this case.
this record will be read in any case.
But based on Null Field Value property value interpretation of col2,3 and 4 value changes.

if Null Field value property='' then these columns 2,3 and 4 are read as Nulls and are treated as Nulls. You can catch them as IsNull(Col2) or IsNotNull(col2).
if Null Field value property is something else or not set, in that case teh columns 2, 3 and 4 are treated as empty values or empty strings. You can cath these columns as Col2=''or Col3='' and Col 4=''.

if these columns are Char or Numeric type this record would be dropped and not read.

[lshort]

a|100
b|200
c|
d|400

in the Sequential File Stage you must specify three things.
1) Nullable = Yes :allow the field to be nullable
2) IntegerType (Default) = 'null' :this makes any input error a null
3) Nullable (Null Field Value) = \000 :set the value of a field that contains a null

in "View Data" the data will appear as:

a 100
b 200
c NULL
d 400

[ukyrvd]

thanks for the input.. this morning I got some time and ran couple of tests.

1) varchar field
2) field defaults on format tab set to ''

==> if the filed is defined as NULLABLE then above setting is causing DS to catch null using isNULL
==> if the field is defined as NNN-NULLABLE DS is not recognizing them as NULL even with above settings

If you dont want to define '' as null for all fields , you can go to that specific column definition in grid and select "Edit row" and specify default null-filed-value for that one filed only.

[roy]

In any case reading frm seq files should be using varchar types regardles of real type since they are actually varchar (unless you have a fixed width file).
using other types might cause you problems.

having fields specified not null able means they can't be tested for null value sine they can't contain it, there are definitions for substitution characters.
As many of us see in the famous warning of null able fields not having null handling it mihgt cause the job to fail.

IHTH,

[lshort]

I hold a different view about datatyping and sequential files. I have found that by applying datatypes and null handling carefully I can avoid a lot of manually coded data validation. ie. If i specify a date type with the proper format applied the sequential file will check the date validation for me...If I specify a field as decimal or numeric the stage will check that as well...and so on.

Just my 2 cents. (and since im in Canada that is about -25)