Lookup

[adams06]

Hi all,

My job looks like


lookup
|
|
source--------------->x---------->target

In my source data

column (A)

10
20
30

Look up data
column(A) column(B)

10 name1
10 name2
10 name3
20 name1
20 name2
20 name3

My problem is when i do a look up i am getting only one record from the look up, But i am supposed to get all the records related to 10( as there are unique), similarly for 20.

any idea.


Thanks in Advance

[bchau]

In DS SE, you can only do this if the reference is an ODBC stage. It's been a while since I have done it, but I believe that the Server Job Developer's guide has details on how to do it (look for "Multirow Lookup for Reference Inputs").

The easier way to do it would be an SQL User defined query in your source stage (Using a Join).

[ShaneMuir]

If your lookup is a hash file you can use a UV stage to bring back multiple rows (I think). Never actually done it myself, but I am sure I have seen many postings about it on the forum.

[ray.wurlod]

That's right, multi-row return from reference lookup is supported in ODBC and UV stage types. Search the forum or consult on-line help for more information.

[chulett]

Yes, you can. It's a wee bit of a PITA but it certainly can be done. There are many postings with the gory details - VOC record, CREATE.INDEX, BUILD.INDEX, creating a hashed file versus a UV table, composite index versus multiple singleton indexes, yada yada yada.

I avoid the need to do that unless you absolutely cannot find another way to implement it.