handle_null() to replace one column with another when NULL

[zulfi123786]

Hi

There is a simple derivation where a source date column which is Nullable is to be replaced with NOT NULL date column when the source column is NULL.

Tried the below:

Code: Select all

TST=handle_null(KEY2,KEY1)

before trying with dates I tested for varchar columns, KEY2 is the source column and KEY1 is the NOT NULL column but it looks like handle_null function takes only scalar values and is not picking up the actual value in the column KEY1

Result when all KEY2 are NULL

abc,KEY1
abc,KEY1
abc,KEY1

And I dont want to use a transformer with If then else clause for such simple thing. Any ideas

Thanks

[chandra.shekhar@tcs.com]

Is handle_null a system function ?
The KEY1 column should contain date value, any other data type values will throw error.

[zulfi123786]

Is handle_null a system function ?

Its not a system function, its a modify operator function name.

The KEY1 column should contain date value, any other data type values will throw error.

Did not get this ?

[chandra.shekhar@tcs.com]

According to your requirement if KEY1 is null then value of KEY2 column should be mapped, then the data type of both KEY1 and KEY2 should be same. So

Code: Select all

handle_null(KEY1,'2013-01-01')

will work but

Code: Select all

handle_null(KEY1,245)

will not work.

[zulfi123786]

According to your requirement if KEY1 is null then value of KEY2 column should be mapped, then the data type of both KEY1 and KEY2 should be same.

It is the same,

before trying with dates I tested for varchar columns

From the above I meant both KEY1 and KEY2 were varchars.