Check for Blank and NULL

[rcil]

What is the differece between two single quotes with out a space and @NULL Ex: Amtbilled = '' and Amtbilled = @NULL. Is it mean the two single is to check for a blank ( no space) and the second @NULL is to check for nulls?

Hope someone can confirm this. Do we have to check for both all the time or is there way that I can do both checks in one single function. I think ISNULL function is to check only for nulls not the blanks.

thanks

[ArndW]

rcil,

you are correct, DataStage considers @NULL and "" to be different.
ISNULL("") evaluates to 0 (FALSE).

Code: Select all

(ISNULL(InColumn) OR TRIM(InColumn)="")

will return 1 if InColumn is either NULL or empty or contains only spaces.

[ray.wurlod]

That's because "" and @NULL are different.

"" is a known value. It is a character string containing precisely zero characters, and can be compared with other character strings, participate in concatenation and substring (!), and so on. It's called "the empty string" or "zero-length string".

NULL is unknown. If you use it in arithmetic the result is null ("there is an unknown number of apples in this barrel - I add three more - now how many apples are in the barrel?"). If you use it in any expression or function the result is null (with some exceptions, where a fatal error occurs, for example the second argument of Oconv or Iconv).

Blank, incidentally, is different again. A single blank character (" ") is a character string containing exactly one character. In ASCII the code point for this character is 32 (or 0x20). It's also called a space character.

In order that things can keep going there are two artificial processing rules in DataStage.

• Len(@NULL) returns 0.
  
  If the test expression in an IF statement is null, then the ELSE path is taken (on the grounds that the THEN path can not be taken because we can not, with certainty, assert that null is "true").

The OP (rcil) is correct in stating that IsNull() is used for testing for null, and regular comparison operators are used for testing for "".

[PhilHibbs]

What is the differece between two single quotes with out a space and @NULL

While the other answers so far are generally correct, there is one huge exception.

If you write them out to a sequential file, and then read them back in again, then there is no difference. Empty strings are read in as @NULL.

Maybe I should clarify that. By default, @NULLs are written out as a blank string. You can change this by changing the properties on the Seq File stage so it writes out a character of your choice when in encounters @NULL, and reads in said character as an @NULL (and presumably then a blank string would be read in as a blank string). It is a little annoying that there isn't a way of setting this up globally.

[ray.wurlod]

Empty strings are only read from text files as null because the "representation of null" field on the Format tab contains "".

That's what causes the conversion, the default representation. There's nothing to stop you changing that, after which "" will be read as "".