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.
Identifying Null values - "|||"
Moderators: chulett, rschirm, roy
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.
This simple piece of code checks both conditions and returns TRUE if the value exists.
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
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: