Lookup

IBMDS · Post by **IBMDS** » Mon Nov 20, 2006 5:08 pm

I have a scenario like this

In source i have multiple values in lookup table i have distinct records

Source table

column1

xyz all a
xyz-all
xyz - all
rxz
rxzab - 1
rxzab - all

In lookup table

Lcolumn1 Lcolumn2 Lcolumn3

xyz 2 1
rxz 3 2

i need to get the ouput like this

Column1 Lcolumn2 Lcolumn3

xyz all a 2 1
xyz-all 2 1
xyz - all 2 1
rxz 3 2
rxzab - 1 3 2
rxzab - all 3 2

Please suggest me regarding this,

Thanks in Advance

ray.wurlod · Post by **ray.wurlod** » Mon Nov 20, 2006 5:30 pm

It's an inner join, isn't it? Therefore a Join stage would seem apposite. Upstream of that stage, generate the lookup value (a substring of the source key). If the reference data set is sufficiently small, a Lookup stage may be more performant. Return all columns except the key from the right/reference input.

thebird · Post by **thebird** » Mon Nov 20, 2006 5:36 pm

Hi,

Assuming that every data in the first column of your reference table is going to be having a length of 3, you can accomplsh this using a Lookup stage or a Join stage (depending on the reference data volume).

If using a lookup stage, then create an additional column from your source link containing the first 3 characters (you can use the substring function in the Modify stage for this) and then this new column can be made as the Lookup/Join key with the reference link. To your output you can pass the the original column1 and columns 2 and 3 from the reference.

I believe this should get you what you are loking for.

Regards

The Bird

thebird · Post by **thebird** » Mon Nov 20, 2006 5:40 pm

thebird wrote: If using a lookup stage, then create an additional column from your source link containing the first 3 characters (you can use the substring function in the Modify stage for this) and then this new column can be made as the Lookup/Join key with the reference link. To your output you can pass the the original column1 and columns 2 and 3 from the reference.

You can use the same approach when using the join stage too.

The Bird

samba · Post by **samba** » Mon Nov 20, 2006 6:15 pm

Thanks Ray and the bird

Its not inner join and length is not fixed in source table and lookup table.

ray.wurlod · Post by **ray.wurlod** » Mon Nov 20, 2006 7:12 pm

It IS inner join

Code: Select all

WHERE SUBSTRING(left.key,1,3) = right.key

You just need to do the substring piece in an upstream stage.