ISNULL is returning 0 for NULLs

[gagan8877]

Hi Gurus

I have 2 fields both - CHAR(12) Not Null columns coming from Complex Flat file, which goes through Joins (no change to column metadata) and enters Transformer1, which has:

If DS.ID1 = '000000000000'
Then If DS.ID2 = '000000000000'
Then SetNull()
Else DS.ID2
Else DS.ID1

and the output column ID gets converted to VARCHAR(50) NULL

The output goes via the next transfomer#3, where we check if it is NULL by using ISNULL function.

The output goes to a sequential file. The ISNULL function returns 0 and the sequential file shows NULNULNULNULNULNULNULNULNULNULNULNUL (Nulls 12 times) values in Notepad++. Converting it to Hex16 also shows all zeros.

So is it putting NULLs ? If yes, then why ISNULL is not working?

How can I check to see what is the value and how can I reject the NULLs?

Thanks

[ray.wurlod]

Because CHAR(12) requires twelve characters and APT_STRING_PADCHAR is set to \x00.

[gagan8877]

Because CHAR(12) requires twelve characters and APT_STRING_PADCHAR is set to \x00.

Thanks Ray, so how can I detect 12 nulls? bcoz ISNULL doesn't work. I need to check if the value contains NULLs and then reject it. I also added the $APT_STRING_PADCHAR to job parameters and the value is 0x20 (space). Still the results are the same.

[gagan8877]

Because CHAR(12) requires twelve characters and APT_STRING_PADCHAR is set to \x00.

I will really appreciate any help on this. I am sure this is not a big deal for the gurus.

[JoshGeorge]

Try doing the 'If Then Else' part in a stage variable. Set APT_STRING_PADCHAR \x00 and Trim DS.ID1 and DS.ID2 in the else part. You can also try to bring both the input fields as varchar instead of char to test for success.

If DS.ID1 = '000000000000'
Then If DS.ID2 = '000000000000'
Then SetNull()
Else DS.ID2
Else DS.ID1

[gagan8877]

Try doing the 'If Then Else' part in a stage variable. Set APT_STRING_PADCHAR \x00 and Trim DS.ID1 and DS.ID2 in the else part. You can also try to bring both the input fields as varchar instead of char to test for success.

If DS.ID1 = '000000000000'
Then If DS.ID2 = '000000000000'
Then SetNull()
Else DS.ID2
Else DS.ID1

Thanks a lot JoshGeorge! I have been struggling with this since quite sometime now. Changing the data type worked

So the rule of thumb here is that all columns need to be VARCHAR and not CHAR, BEFORE THE JOIN and they need to be NOT NULL, then you can check for the length to determine, if the result was NULL or not. I hope they fix the IsNull in future.

[ray.wurlod]

There's nothing wrong with the IsNull() function.

You just didn't understand the implications of using Char(N) data type which, perforce, must contain N characters. It's unfortunate that this was never enforced in server jobs.