Lookup with Blank

[just4geeks]

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!

[chulett]

I would think it should work if both fields have two spaces in them, and I'm assuming that's what you mean by "blanks", yes?

[just4geeks]

Thanks Craig.
Although Key column is defined as CHAR(2), When I am calculating the length of the blank column from Lookup file, Lenght is coming as 1. I am using function Len(Keycol). Thats why I think lookup is not matching. Any idea?

Thanks!

[just4geeks]

Basically how blank values are coming as from Lookup Hashed file is my questions. are they coming as "" or " " ( one space) or " " ( 2 spaces) or any other value. Length function always returns 1 though for blank value.

Thanks,

[ArndW]

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.

Code: Select all

LEN(Char(32):Char(32))

is going to be 2.

[just4geeks]

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.

Thanks Andrew!

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!

[just4geeks]

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.

Thanks Andrew!

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!

[chulett]

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.

[anu123]

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.

Thanks Andrew!

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

[chulett]

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.

[just4geeks]

Thanks Craig. You are right.

We have worked out the solution keeping limitation of Server Edition in Mind.

Thanks all who contributed and visited this topic.

Take care!