DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
Developer9



Group memberships:
Premium Members, Heartland Usergroup

Joined: 14 Apr 2011
Posts: 167

Points: 2060

Post Posted: Wed Sep 13, 2017 3:25 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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:
oracle (source) > Leftouter Join >XFM > DataSet >cdc >Flatfile

Transformer derivation ,I wrote
Code:
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42107
Location: Denver, CO
Points: 216145

Post Posted: Wed Sep 13, 2017 3:33 pm Reply with quote    Back to top    

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

_________________
-craig

Can't keep my eyes from the circling skies
Tongue tied and twisted just an earth bound misfit, I
Rate this response:  
cdp



Group memberships:
Premium Members

Joined: 15 Dec 2009
Posts: 111
Location: New Zealand
Points: 1568

Post Posted: Wed Sep 13, 2017 5:03 pm Reply with quote    Back to top    

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/knowledgecenter/en/SSZJPZ_11.3.0/com.ibm.swg.im.iis.ds.parjob.dev.doc/topics/r_deeref_Null_Handling_Functions.html
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours