Handling Nulls for decimal fields in transformer stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Developer9
Premium Member
Premium Member
Posts: 187
Joined: Thu Apr 14, 2011 5:10 pm

Handling Nulls for decimal fields in transformer stage

Post by Developer9 »

Hi,
My requirement is If there are NULLs present in source (Oracle table) for decimal fields then I need to convert them into Blanks in target (Flat file ).

Code: Select all

oracle (source) > Leftouter Join >XFM > DataSet >cdc >Flatfile
Transformer derivation ,I wrote

Code: Select all

If IsNull(lnk_col1) Then '' Else lnk_col1
After I perform the "Left outer Join" ,these decimal fields returning 0 (Zero) instead NULL .

I read the forum posts ,Its the expected behavior for decimal fields .But How Can I perform null handling for decimal fields ?

Please suggest me how to handle this in transformer stage ?

Thanks.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

From what I recall, you would need to define them as string fields in the flat file and then format them accordingly.
-craig

"You can never have too many knives" -- Logan Nine Fingers
cdp
Premium Member
Premium Member
Posts: 113
Joined: Tue Dec 15, 2009 9:28 pm
Location: New Zealand

Post by cdp »

Are your decimal columns nullable in both of the Input links of your Join Stage ?
If you have defined your decimal columns as non-nullable in DataStage, I think it will default the NULLs to 0 ?

Also, you may prefer to use the NullToEmpty() function instead of the If IsNull() Then '' .. Else ... syntax
https://www.ibm.com/support/knowledgece ... tions.html
Post Reply