TrimLeadingTrailing NULLS
Moderators: chulett, rschirm, roy
TrimLeadingTrailing NULLS
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?
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?
Re: TrimLeadingTrailing NULLS
Did you try something like this ?
If NullToValue(lnk_in_tfm.Gender,'$')='$' Then 300 Else 0
If NullToValue(lnk_in_tfm.Gender,'$')='$' Then 300 Else 0
--
Raj
Raj
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.
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.
-
- Participant
- Posts: 32
- Joined: Fri Nov 07, 2014 11:18 am
Re: TrimLeadingTrailing NULLS
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)
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)
that's wierd ?
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.
I'm not familiar with that particular function but I'd wager that all it trims, leading and trailing, are spaces.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.