NULL created on NOT NULL columns at Join or Lookup Stage

[Jewels]

EMPTY STRING CONVERSION or NULL CREATED ON NOT NULLABLE COLUMN

When performing a Left Outer Join to a secondary input where a NOT NULLABLE column on the secondary input is required for output, but the primary record does not find a match on the secondary input, a Null Representation is forced into the not nullable column. Normally for VARCHAR this is Empty String aka Hex(00) represented in DataStage as 2 double quotes (""), CHAR should be spaces for the length of the field and integer or numeric should be zero.

Sometimes the normal Check and conversion of the Empty String null representation doesn't work (I don't know why) even though the osh query selects the record when the column is set equal to Empty String (osh "filter -where 'INPUT_FIELD = \'\'' < DataSet.ds | peek -name -all").

When writing to a comma quote delimited Sequential File, the Empty String appears as a space but this is not the normal representation, it should appear as quote quote with nothing between.

Here is a resolution I've found to work for me since I haven't found anything else easily searching DSXchange.

In a transformer, perform the following check:

If INPUT_FIELD = char(00) Then "Output Value" Else INPUT_FIELD