Hash File Lookups

[JezT]

I have created a hash file of 2 columns containing different ID's. The first column contains Key ID's and the second column contains the subsidiary ID's. So for example, there could be several occurences of an ID in Column 1 but all the ID's in Column 2 would be unique.

Col1, Col2
1,3
1,4
1,5
2,6
2,7
2,8

So as the above shows, the Key ID of 1 has 3 subsidiary ID's of 2,3 & 4.

In my Job I want to pass the Key ID into the Hash File and bring back all the subsidiary ID's for that Key ID. At the moment, I am only managing to bring back 1 subsidiary ID.

Any ideas on how I can bring them all back ?

Cheers
Jez T

[Sainath.Srinivasan]

Store as concanetated values in a single column separated by some value.

[ml]

maybe you can put the reference link as the principal, and the principal as reference.

[ray.wurlod]

Your requirement is not something that a hashed file is geared to do; the whole raison d'etre of a hashed file is to return THE row whose key is given.

You would not even be able to populate your proposed hashed file as you wish; you would end up with one row for each key value; the most recently written row.

You can use any SQL-based stage (including a UV stage) to achieve the effect you want. If you use an ODBC stage or UV stage, DataStage can manage multi-row returns from the reference lookup. Search the on-line help from Designer for multiple row.