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
Check for Blank and NULL
Moderators: chulett, rschirm, roy
rcil,
you are correct, DataStage considers @NULL and "" to be different.
ISNULL("") evaluates to 0 (FALSE).
will return 1 if InColumn is either NULL or empty or contains only spaces.
you are correct, DataStage considers @NULL and "" to be different.
ISNULL("") evaluates to 0 (FALSE).
Code: Select all
(ISNULL(InColumn) OR TRIM(InColumn)="")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
"" 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").
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Re: Check for Blank and NULL
While the other answers so far are generally correct, there is one huge exception.rcil wrote:What is the differece between two single quotes with out a space and @NULL
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.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 "".
That's what causes the conversion, the default representation. There's nothing to stop you changing that, after which "" will be read as "".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.