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
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?
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
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