DSXchange

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

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

The if expression is not evaluating to true. It is not inserting even "NULL" instead it's inserting 0

Also it says @NULL not defined. I do not see @NULL defined under system variable

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

I think that is all of them?
I only see a few also...

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.

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.

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