Lookup table with multiple values as output!!

[paranoid]

Hi,

We have a requirement where we require multiple matching rows from refernce data to be outputed out of a lookup. We are using Datastage Server Jobs. Can someone help us on how this can be achieved through Server Jobs

Eg:

Master File/table:
Id Name
1 A
2 A
3 B
5 C
6 C
7 C
8 C
9 C

Refernce File/Table
ID Feild1
1 A
2 A
3 B
5 C
6 C
7 C
8 C
9 C
X 1
Y 2
U X
V Y

Output expected:
ID Name Field1
1 A A
2 A A
X 1 A
Y 2 A
U X A
V Y A

[ArndW]

I don't know if it is just me being tired, but I can't figure out your logic. Your source has 8 rows, reference has 13 and output 6. Your output key seems to come from the reference file, but the output makes it seems as if you are retrieving the "Feild1" <sic> value. Could you perhaps explain the logic or use unique column names to make it easier to understand the example?

[chulett]

Regardless, you can only get multiple records back from a lookup by using the ODBC or UV stages and by enabling that option on the lookup link itself. Search for "multi-row result set" here for many discussions on the subject.

[ray.wurlod]

Looks like there's a "<> 'C'" condition somewhere in the logic too. Do provide us with your specification of business logic.