Checking for Null value

sankar18 · Post by **sankar18** » Tue Dec 17, 2002 4:19 am

For Null reject rows
--------------------
In Constraint field,

Len(Trim(column))0

T Sankar

Edited by - sankar18 on 12/17/2002 06:35:31

msigal · Post by **msigal** » Tue Dec 17, 2002 8:22 am

My experience with Null data is that the transformer converts it to spaces (making sankar18's suggestion work well). But when written out to a sequential file the null will be written, not a space. If spaces are ok to pass and nulls aren't you'll need to check for the null with a custom routine. When you pass the column to the routine, the routine will recognize the null values. If you need to check for nulls byte by byte you can use the Seq() function in your routine.

Myles Sigal
Technical Analyst
The MEDSTAT Group
777 E. Eisenhower - 435B
Ann Arbor, MI 48108

myles.sigal@medstat.com
734-913-3466

chulett · Post by **chulett** » Tue Dec 17, 2002 9:58 am

Hmmm.. I don't recall having issues with null data getting turned into spaces in the transformer. Usually, people just think a field is null when in fact it is not. I'm curious what 'subbu' would see in the debugger as his job ran when it processes the row with the null. Does the watch variable show the value as 'NULL' or just blank? Your field may be coming in with spaces in it, which if it does it will not test as true with IsNull(). If there is a possibility of mixing spaces and nulls in fields, I'll usually test for that and explicitly set the field to @NULL before processing it further.

-craig

msigal · Post by **msigal** » Tue Dec 17, 2002 10:21 am

Craig - I've debugged with a file that had nulls and nulls mixed with characters and found that the debugger showed spaces where the nulls were. Additionally, I used Seq(input[byte,1]) in a separate stage variable and when I debugged the stage variable returned 32, space, 0 for null. You kindly assisted with that issue in the topic "Reappearing Nulls". - Myles

ray.wurlod · Post by **ray.wurlod** » Tue Dec 17, 2002 4:58 pm

There are two issues here.
The first is, what does the original poster mean by "null". Context suggests that what is being meant is, in fact, "zero-length string", which is definitely not the same as NULL (unknown value). One tests for zero-length string with comparison operator or Compare() function, one tests for NULL with IsNull() function. It is not possible to compare anything to the @NULL system variable (though such a comparison IS possible to the @NULL.STR system variable, which contains "CAST (NULL AS CHAR(1))".
The second issue is whether true NULL is ever converted in a Transformer stage. As far as I am aware, this does not happen, though it can occur if there is a sequential file stage on the other end of a link (there is capability on the Format tab to specify how NULL is handled in sequential file stages).

As far as the original problem is concerned, if Not(IsNull(column)) is accepting the row, then column is not NULL.
It will become necessary (and the Seq() function and/or the Len() function is a good way) to find out exactly what is in the column from the input link).