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
Null handling for integer field
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
Re: Null handling for integer field
Int will hold 0 check if col = 0 will work.
DS User
DS User
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
-
- Participant
- Posts: 31
- Joined: Mon Jun 28, 2010 5:15 am
- Location: pune
Re: Null handling for integer field
i am a little curious about this len()function. How can length be '', it can only be numeric value, right?vishal_rastogi wrote: in transformer i am using if len(trim(link1.columnA)) = '' then 1 else 0
How about using if '' then 0 else as it is?
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
-
- Premium Member
- Posts: 132
- Joined: Tue Sep 04, 2007 11:38 am
- Location: NOIDA
-
- Participant
- Posts: 47
- Joined: Thu Dec 09, 2010 4:37 am
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).
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).