DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
perspicax



Group memberships:
Premium Members

Joined: 07 Dec 2017
Posts: 14
Location: USA
Points: 119

Post Posted: Wed May 16, 2018 12:10 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Is there a way to convert blank space to NULL. We have fields from the source application that can have ' ' representing NULL. We would like to load NULL in DW.

IF (LEN(TRIM(lnk_from_src.FIELD_XYZ," ","A")) = 0 or lnk_from_src.FIELD_XYZ, = " ")
THEN "NULL"
ELSE lnk_from_src.FIELD_XYZ

The above is not working as expected. It is inserting 0
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42581
Location: Denver, CO
Points: 219161

Post Posted: Wed May 16, 2018 12:21 pm Reply with quote    Back to top    

THEN "NULL" with the double-quotes will convert it to the word NULL. Try @NULL (without the quotes) rather than "NULL".

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
perspicax



Group memberships:
Premium Members

Joined: 07 Dec 2017
Posts: 14
Location: USA
Points: 119

Post Posted: Wed May 16, 2018 12:33 pm Reply with quote    Back to top    

The if expression is not evaluating to true. It is not inserting even "NULL" instead it's inserting 0
Rate this response:  
Not yet rated
perspicax



Group memberships:
Premium Members

Joined: 07 Dec 2017
Posts: 14
Location: USA
Points: 119

Post Posted: Wed May 16, 2018 12:34 pm Reply with quote    Back to top    

Also it says @NULL not defined. I do not see @NULL defined under system variable
Rate this response:  
Not yet rated
perspicax



Group memberships:
Premium Members

Joined: 07 Dec 2017
Posts: 14
Location: USA
Points: 119

Post Posted: Wed May 16, 2018 1:20 pm Reply with quote    Back to top    

I used setnull() and it works. I think @NULL works only in server jobs.

Maybe this is for another topic, I only see 5-6 system variables in parallel job while using transformer. Is there any setting I need to change to see all of them?

Thanks
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 327

Points: 3307

Post Posted: Wed May 16, 2018 1:40 pm Reply with quote    Back to top    

I think that is all of them?
I only see a few also...
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42581
Location: Denver, CO
Points: 219161

Post Posted: Wed May 16, 2018 1:50 pm Reply with quote    Back to top    

Right, sorry - that was a Server memory that bubbled up to the top of the punchbowl first. That setnull() function is the appropriate choice for a Parallel job. The "all of them" system variables are only valid in Server jobs or routines from what I recall, there's just a small subset available for Parallel jobs.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42581
Location: Denver, CO
Points: 219161

Post Posted: Wed May 16, 2018 1:53 pm Reply with quote    Back to top    

perspicax wrote:
It is not inserting even "NULL" instead it's inserting 0

I assume that's because your target field is a number rather than a string and you're seeing the result of an implicit conversion.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 413
Location: New York City
Points: 4531

Post Posted: Wed May 16, 2018 4:05 pm Reply with quote    Back to top    

I wonder if the target database is Oracle by any chance? If yes then by just trimming the source field, without any other logic, the database will take take of setting the target field to null

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
Not yet rated
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