SetNull produces 0 instead of NULL for integer column?

[IsNull]

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!

[SURA]

Integer hold the 0 is the reason.

[ray.wurlod]

0 <> "NA"

Nor can "NA" ever be an integer value.

[IsNull]

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?

[ray.wurlod]

How have you set the Null Field Value and Default Value properties for this column?

[IsNull]

Actually I figured it out, this works:

If DSLink2.Ozone = 'NA' Then 'NULL' Else DSLink2.Ozone

I was thinking this too complicated, thanks for your insights!

[abhijain]

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.

[IsNull]

@abhijain: very good point, thank you !