Identifying Null values - "|||"

[vnspn]

Hi,

We are currently loading data from an external source to a Sequential File. I have certain records containing NULL value for a particular column. I need to filter out those records. Those NULL values show in the output flat file as "|||||". I'm not able to filter out those records using the function "IsNull".

Any idea on what could be the means for identifying these NULL values.

Thanks.

[kcbland]

There's NULL (ASCII 0 ) and then there's blank (zero length string). The only way to detect a NULL is using the ISNULL() function, but zero length string can be checked with LENGTH() and comparison to "".

So, you probably need to check for both because when DS Server writes a NULL to a sequential file it actually turns it into a blank, thus two field delimiters side-by-side. But, when it writes a blank, it writes a blank. There's no way to tell what the data was originally, because NULL and blank are written as a blank. So, when comparing, you need to consider both situations.

Either write a Transform or a Function to handle both cases, maybe something like an "Exists" Transform/Function.

Code: Select all

If ISNULL(Arg1) OR Arg1 = "" Then @FALSE Else @TRUE

This simple piece of code checks both conditions and returns TRUE if the value exists.

[vnspn]

Thanks for your reply Kenneth. Yes, I did try to handle both these conditions. The NULL value doesn't get recognized by both of these conditions.

Is there any more way of handling the NULL values?

[narasimha]

The value might not be NULL then, view the data in your sequential file using a hexeditor and try to identify what they actually are.

[kcbland]

Then it's not a NULL or has some length other than zero. This implies that there's something there, however you're attempting to view it is not sufficient. A hex editor is a great way, using Windoze Notepad is not. Consider writing an extra output column that contains the length of the value written to the preceding column. Better yet, if the length is 1, consider writing the ASCII value out. You could be missing a high/low ASCII value that is not easily viewable.

[vnspn]

Yes, you are correct. It was not NULL but the column had contained binary zeros in them. I used CHAR(000) to identify them and the trim those columns.

Thanks for your inputs.

[ray.wurlod]

A useful trick is that Len(@NULL) returns zero. Len("") also returns zero.
Len(Char(0)), however, returns 1.