Convert space to null

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
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Convert space to null

Post by perspicax »

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
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

The if expression is not evaluating to true. It is not inserting even "NULL" instead it's inserting 0
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

Also it says @NULL not defined. I do not see @NULL defined under system variable
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

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
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

I think that is all of them?
I only see a few also...
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

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
Post Reply