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 !
Multiple Row Lookups
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
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.evanmaas wrote:"Reference Link with multi row result set" can you only use in ODBC stage
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Fri Apr 30, 2004 9:11 am
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".