Problems with Null representation

[avi21st]

Hi

I am having a strange problem with Datastage

In the job I am getting Input File as fixed width and in place of null we have blank.

I generally TRIM then value and then check ISNULL - then handle it accordingly{ ISNULL(TRIM(LinkName.ColumnName)}

But Datastage cannot understand that TRIM(" ") is equal to Null.

Even I tried in a routine:

Code: Select all

If TRIM(' ') = @NULL Then Ans='NULL WORKING' Else Ans='NULL NOT WORKING'

But I am always getting 'NULL NOT WORKING'

But generally as far sa I know I always handle Null with the ISNULL. I have no clue-please help me in this regard.

Is there a internal representation of NULLs in Datastage- if so what is the representaion.

Thanks

[I_Server_Whale]

I think DataStage is performing correctly. Trimming all spaces will result in an "empty" string and not in a NULL.

That is why,

TRIM(' ') can never be equal to NULL.

If you are trying handle spaces and NULLS in the incoming data, you can use to this code to check for both:

Code: Select all

If Len(Trim('InputLink.InputCol)) = 0
Then "it is spaces or NULL"
Else "It is not spaces or NULL"

Hope that helps,

Naveen.

[kcbland]

There is a difference between blank and NULL. NULL is a physical character on the ASCII chart, blank is a zero length string. You can NEVER, in SQL or DS BASIC, compare ANYTHING to a NULL. You can't do math on NULL values either. That's why there's a specific function in DS BASIC and SQL to handle the NULL value.

You should consider writing either a DS Function or DS Transform to handle both cases. I like to use a function called something like EXISTS.

DS Function logic for EXISTS:

Code: Select all

IF TRIM(Arg1) # "" OR NOT(ISNULL(Arg1)) Then Ans=@TRUE Else Ans=@FALSE

Now, you can just check if data "exists" using this function. It correctly handles both the blank and NULL condition.

[ray.wurlod]

You may find the following conceit useful. It applies in DataStage BASIC, please do not assume that it can be generalized beyond that.

LEN("") returns 0.

LEN(@NULL) also returns 0.

That is, Len(X) returns 0 both when X = "" and when X IS NULL.

[NBALA]

Thanks Ray and guys.

I have faced similar issue with spaces in the date field from mainframe source. Before reading this post I used

Code: Select all

  If IsNull(Trim(lnk1.colA)) Then <defualt date> else lnk1.colA

It never worked.

Thanks again guys.

-NB