Lookup with Blank
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Lookup with Blank
We have a situation where in certain conditions, I need to look up blank values with blank values.
How can we match Blank values coming from Hashed file with blank coming from source file.
' ' from source = ' ' from lookup? this is not working
Key field is defined as CHAR(2) in source as well as lookup.
Please suggest.
Thanks in advance!
How can we match Blank values coming from Hashed file with blank coming from source file.
' ' from source = ' ' from lookup? this is not working
Key field is defined as CHAR(2) in source as well as lookup.
Please suggest.
Thanks in advance!
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Blanks are not modified or trimmed automatically in DataStage. A Char(2) definition will have length 2 regardless of contents, otherwise it is a VarChar column. Hmm, I just saw that you posted in PX but marked it as a server job. Data is not typed strictly in server and you could get a length of 1. is going to be 2.
Code: Select all
LEN(Char(32):Char(32))
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Thanks Andrew!Data is not typed strictly in server and you could get a length of 1.
Code:
LEN(Char(32):Char(32))
is going to be 2.
It is indeed server job. I apologize for posting it to parallel forum. I am wondering how our situation can be handled if data type is not strictly followed in Server job. Any lights?
Thanks!
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Thanks Andrew!Data is not typed strictly in server and you could get a length of 1.
Code:
LEN(Char(32):Char(32))
is going to be 2.
It is indeed server job. I apologize for posting it to parallel forum. I am wondering how our situation can be handled if data type is not strictly followed in Server job. Any lights?
Thanks!
Attitude is everything....
Server is quite tolerant of diversity and doesn't run the police state that PX does so will quite happily put "OH NOES!" in a Char(2) or even a Decimal. The metadata wouldn't be enforced until it hits the target and then not always, depending on the target itself.
So the onus falls on you to ensure the metadata is enforced and a Char(2) doesn't have more or less than two characters in it. And having said that, if you are vigilant, your lookups should work fine, even "blank to blank".
ps. It's Arnd not Andrew.![Wink :wink:](./images/smilies/icon_wink.gif)
So the onus falls on you to ensure the metadata is enforced and a Char(2) doesn't have more or less than two characters in it. And having said that, if you are vigilant, your lookups should work fine, even "blank to blank".
ps. It's Arnd not Andrew.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
just4geeks wrote:Thanks Andrew!Data is not typed strictly in server and you could get a length of 1.
Code:
LEN(Char(32):Char(32))
is going to be 2.
It is indeed server job. I apologize for posting it to parallel forum. I am wondering how our situation can be handled if data type is not strictly followed in Server job. Any lights?
Thanks!
I would have a check on the particular field to see and MASK 'BLANK' values with a value that you never expect in that field say 'HI'. I think they match once you have masked blanks on both inbound and lookup streams.
Thanks
Thank you,
Anu
Anu
No need, "blanks" aka spaces work just fine here and are handled no different than any other character. Like all reference lookups, they just need their values to match on both 'sides' of the key, Char(2) to Char(2).
Just to put the last nail in this, I built a simple test job that creates a hashed file with a single record keyed by a Char(2) field populated with two spaces, then sent the same thing in as the input data. As expected, the lookup worked fine.
Just to put the last nail in this, I built a simple test job that creates a hashed file with a single record keyed by a Char(2) field populated with two spaces, then sent the same thing in as the input data. As expected, the lookup worked fine.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA