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.
Join Stage adding unidentified characters to output
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 57
- Joined: Tue Jun 30, 2009 9:38 am
Re: Join Stage adding unidentified characters to output
check what is set in $APT_PADCHAR variable
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 4
- Joined: Tue Apr 19, 2011 12:11 pm
Re: Join Stage adding unidentified characters to output
[can u explain clearly i didnt get]
hasi
datastage developer
datastage developer
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
Re: Join Stage adding unidentified characters to output
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.
Thanks & Regards
Jose Johny
Project Engineer
Wipro Technologies |Bangalore
"Life is a process of cultivating goodness & removing evilness"
Jose Johny
Project Engineer
Wipro Technologies |Bangalore
"Life is a process of cultivating goodness & removing evilness"