Null handling for integer field

[vishal_rastogi]

Hi

i have one sequential file1 ---> transformer----> sequential file2
in sequential file1 there is column A(nullable,integer) and i have records like this
'12345'
'23456'
''
'456789'

in transformer i am using if len(trim(link1.columnA)) = '' then 1 else 0

now when i am running the job or viewing the source data third row is not coming
if i am using the field as varchar field it is working fine
please suggest

[SURA]

Int will hold 0 check if col = 0 will work.

DS User

[vishal_rastogi]

yes SURA you are right that Int works on 0 but in the integer field i am getting few records like '' this can you please suggest how to handle that

[deeplind07]

in transformer i am using if len(trim(link1.columnA)) = '' then 1 else 0

i am a little curious about this len()function. How can length be '', it can only be numeric value, right?

How about using if '' then 0 else as it is?

[vishal_rastogi]

hi deeplind07

for varchar it will work fine ....

[meet_deb85]

What NULL files Value have you provided for this column at the source sequential file stage

[vishal_rastogi]

i didn't get your point

[ShaneMuir]

My understanding of sequential files is that technically everything in them is a text. They do not contain metadata. It is up to the application which reads the file to interpret the data.

If you tell datastage that they are numbers, my understanding is that it is performing an implied conversion as it reads the values. If the value was empty, and your input field is nullable then chances are the field has a null value.

Since your original check for a blank value failed, I would do an IsNull check instead.

If you want blank fields to be a zero, then you could to set the input field to be not nullable and set the default null value to 0. (Which it usually is by default).