Join Stage adding unidentified characters to output

[bhargav_dd]

Hi

I'm using an left outer join in Join stage to join results returned from 4 Sybase tables and storing into a flat file. As this is an outer join there won't be data in few of the columns. For columns which doesn't have data I need to populate with 0 or spaces.

I'm having problem in determining columns which are not having data as DataStage is trying to populate those columns with unknown characters. These are not null, after my inital research I got to know that Datastage tries to populates coulmns with some default values based on their sql type whenever it doesn't have a value after outer join. Is there a way I can restrict this default behaviour.

PS: I tried to use Len,Trim,StripWhiteSpaces and all Null handling functions, but they didn't work.

Thanks in advance.

[madhukar]

check what is set in $APT_PADCHAR variable

[ray.wurlod]

Any column being returned from the outer side of an outer join should be made nullable. Then you can deal with the nulls in a downstream stage.

[manjuak.boya1@gmail.com]

[can u explain clearly i didnt get]

[jwiles]

Make your columns from the outer link nullable on output from the join. Then you can simply check to see if they are null or not and populate them as you wish.

Also, you can add an indicator column (use boolean values, etc.) to the outer link and use it's value after the join to see. Set it to a value of 1 before the join, if it's not 1 after the join your additional columns (from the outer link) could be considered empty.

Regards,

[josejohny]

Outer join link data column can be given as nullable "Yes" then in downstream stages null can be identified by isnull() or Trim(NullToEmpty(Column name))<>"" functions.