Multiple Row Lookups

[JezT]

I am currently working on a job where I have a list of ID's that relate to certain bank accounts. From these ID's I am attempting to obtain, via a lookup, the Customer ID's of the account holder. For sole accounts this is okay as there is a 1:1 relationship.

However, for joint accounts, there will be 2 different Customer ID's for one single Account ID.

Is there a way I can pass in an Account ID into a lookup and retrieve both of the Customer ID's that relate to that account ?

I realise I cannot do this via a Hash file and was wondering whether I couls do an actual lookup on the DB2 table that stores the data. I have tried it and currently it only brings back 1 record (the latter of the 2 customer ID's). I have seen somewhere that there should be a "Reference Link with multi row result set" check box that I can tick on the transformer stage but I don't seem to have one !

Please help !

[evanmaas]

Hi,

"Reference Link with multi row result set" can you only use in ODBC stage

Regards,

Erik

[Sainath.Srinivasan]

It will be easier to perform a join in the source sql itself.

[roy]

Hi,
There is a trick using hash files !
create the file to hold the customer IDs in an array (i.e. seperated by a @VM for Value Mark)
and in the table definition put that column's type as MultiValue.

IHTH,

[chulett]

"Reference Link with multi row result set" can you only use in ODBC stage

Both the ODBC and UV stages, actually. And the UV stage can be dropped 'over' a hash file, so technically it is possible to do this with hash files. Not very efficient without tweakage, and the hash must be in an account or have a VOC record from what I recall, but possible.

[alhamilton]

Using the ODBC stage will probably be the easiest way. Then, open the transformer stage. Right click in an open space and choose "stage properties". Pick the Inputs tab and the link for your odbc lookup stage. Toward the bottom there should be a box to check for "Reference link with multi row result set".