Identify Null Values ...

[davangeri]

Hi,
I am doing a lookup on a table whose fields (all) have Nullable = No (DB2 Database). I am supposed to write these records to a reject file, if this lookup fails. I have 3 fields that are returned from the lookup table.

The following expression, evaluated at the output of the lookup, does not evaluate correctly -

If (
IsNull(lkp_link.C_CAR_TRK)
OR IsNull(lkp_link.C_FAM_LINE)
OR IsNull(lkp_link.I_CURR_SOLD)
)
Then 1 Else 0

(the above expression evaluates to 0)

Neither is this working -

If (
NullToValue(lkp_link.C_CAR_TRK,'X')='X'
OR NullToValue(lkp_link.C_FAM_LINE,'X')='X'
OR NullToValue(lkp_link.I_CURR_SOLD,'X')='X'
)
Then 1 Else 0

this evaluates to 0 as well.

Need help on how to determine whether the lookup has failed or succeeded.
Kindly excuse if a similar Q has been posted before.


Rgds
Niraj

[talk2shaanc]

Though the table may be having NOT NULLABLE defined, you will have to define all the three columns as nullable in your db2stage, look-up stage, and probably your transformer stage (or stage, where you are doing this null check).

The reason for getting 0 after look-up failure :
The db2 stage extract all the records and non of them are nullable at that time, but after doing look-up some of the input records doesn't find a record in the look-up table and have NULL for all the three fields (lkp_link.C_CAR_TRK, lkp_link.C_FAM_LINE, and lkp_link.I_CURR_SOLD)...but once these fields moves to the next stage ( i/p to transformer), you may be having nullable as "NO" in metadata of transformer. In such cases DS-PX defaults these value to DS -PX defaults.

If defaults are not changed by your administrator; it should be spaces for character, 0 for integer and 0001-01-01 for dates.

Since all the fields are defaulted, your IsNull check doesn't work.