NULL check

[New2DS]

Hello All,

Which is the good idea to check for nulls if the source is a seqfile. Either '' or @NULL if the 'Defalut null string' text filed is blank on the format tab.

thanks

[chulett]

A good idea? Checking for both would make it harder to go wrong.

[ray.wurlod]

Comparison against @NULL is meaningless; the result is necessarily "unknown". That is, you can't legitimately say Col = @NULL and expect it to behave in a Boolean fashion (in fact, there are rules in DataStage that govern the behaviour under these circumstances, but won't yield the results you expect).

The only valid way to test for null is to use the IsNull() function. Therefore to test for either condition being true you need an expression like

Code: Select all

IsNull(InLink.ColumnName) Or (InLink.ColumnName = "")

[chulett]

Comparison against @NULL is meaningless; the result is necessarily "unknown". That is, you can't legitimately say Col = @NULL and expect it to behave in a Boolean fashion (in fact, there are rules in DataStage that govern the behaviour under these circumstances, but won't yield the results you expect).

I was assuming people here have a basic grounding in three value logic and know that you can't check if something 'equals' NULL. I'm loath to hit them with that stick unless it is actually needed... but perhaps, for the sake of the Greater Good, that shouldn't be assumed here.

On a side note, I now use your 'barrel of apples' analogy when needed to make the point... I though it was great! Paraphrasing:

I have an unknown number of apples in a barrel. I now add three more apples to the barrel - how many apples are in the barrel?

[ray.wurlod]

To get a definitive answer we also need to incarcerate Schrodinger's cat in the barrel, and request the answer from the cat. Perhaps Heisenberg could also be engaged as a consultant. Ultimately everything's about quantum, even chaos.