DSXchange

does TrimLeadingTrailing() remove nulls from lookup??


doing lookup upstream onb gender and if lookup fails then being set as continue. This means if lookup fails then the The fields from that link are set to NULL and Continues processing any further lookups before sending the row to the output link.

downstream in a transformer I would like to validate the data.

If gender value is null (means lookup didnt happen properly) then set it to our internal error code else set to 0 (like a soft delete and it is mapped to an output field)


If IsNull(TrimLeadingTrailing(lnk_in_tfm.Gender)) Then 300 Else 0

300 = our internal error code


My question is in the transformer does doing TrimLeadingTrailing from the gender field does it trim null value set by the lookup upstream for lookup failure?

Did you try something like this ?

If NullToValue(lnk_in_tfm.Gender,'$')='$' Then 300 Else 0

i am not doing any transformations at this point so no need to convert null to value.

If IsNull(TrimLeadingTrailing(lnk_in_tfm.Gender)) Then 300 Else 0

will my function trim the null is what I need clarification on??

if it does then proper error code wont be outputted.

Perhaps you should clarify what "trim the null" means as I have no idea. Regardless, it seems like something you could test easily enough.

if lookup doesn't happen then the fields from that link are set to NULL with the Continue option. That is the null i am talking about

In Transformer I am checking and if it is null then set the internal error code (300) else 0

before i check the null I am trimming the value coming in the gender field, so i just want to make sure it doesn't trim null values that the lookup sets for those values where lookup doesn't happen.

Convert Low values to spaces first and then trim the column and then compare with NULL.
What we have seen here is ;
There are cases when a char column may be padded by Spaces or by Lowvalues. (a normal sql client wont show any difference until you look at the hex values for the column)

any other suggestions??

So you are wondering if you trim a NULL (an unknown value, btw) that it might turn it into something else and no longer be recognized as a NULL? It won't... and as noted that would be something you could easily test yourself in the time you've spent waiting for a replies.

I'm not familiar with that particular function but I'd wager that all it trims, leading and trailing, are spaces.

Are you referring to NULL (unknown value) here, or trailing NUL bytes (0x00)? If the latter, you can use the Convert() function to convert any occurrence of Char(0) to "". Or you can use the three-argument version of the Trim() function to trim just the trailing occurrence(s).

I would not expect this function to remove any type of nulls.

TrimLeadingTrailing - Removes all leading and trailing spaces and tabs from a string.