Disclaimer: I'm DataStage noob....
I have a IfThenElse function/derivation in the Transform stage:
If DSLink2.SalesAmount = 'NA' Then SetNull() Else DSLink2.SalesAmount
DSLink2.SalesAmount is an integer column. When populating this data into a sequential file, I get a value 0 instead of NULL in the column. To my understanding, SetNull() should produce NULL, not 0? What I'm missing here? Thanks!
SetNull produces 0 instead of NULL for integer column?
Moderators: chulett, rschirm, roy
Re: SetNull produces 0 instead of NULL for integer column?
Integer hold the 0 is the reason.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks for the reply. My parallel job data flow is
Seq. File --> transform --> seq. File
In the first seq. File there is data in the integer column
1, 5, NA, 8 ...
NA stands for a missing value. My intention is to convert NA --> NULL using
If col = 'NA' Then Setnull() else col
And this produces 0 instead of NULL in the text file. This is wrong because 0 denotes as a true integer value rather than a missing value. Anu ideas?
Seq. File --> transform --> seq. File
In the first seq. File there is data in the integer column
1, 5, NA, 8 ...
NA stands for a missing value. My intention is to convert NA --> NULL using
If col = 'NA' Then Setnull() else col
And this produces 0 instead of NULL in the text file. This is wrong because 0 denotes as a true integer value rather than a missing value. Anu ideas?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Good that you figured it out. but This can be handle using below logic as well.
Since your source is Sequential file. the particular INTEGER column can be read as "Varchar"
In transformer, you can validate it using below logic
If Num(Integer Column) Then Integer Column Else SetNull()
And, since your target is also Sequential file. You can set the file properties
- NulltoValue as ' '
this will give you empty in the target file, if your 'NA' or non-integer value appear in source.
This way, you will be able to handle all Non-integer values and not only NA.
Since your source is Sequential file. the particular INTEGER column can be read as "Varchar"
In transformer, you can validate it using below logic
If Num(Integer Column) Then Integer Column Else SetNull()
And, since your target is also Sequential file. You can set the file properties
- NulltoValue as ' '
this will give you empty in the target file, if your 'NA' or non-integer value appear in source.
This way, you will be able to handle all Non-integer values and not only NA.
Rgrds,
Abhi
Abhi