Regarding Multi row return
Moderators: chulett, rschirm, roy
-
georgesebastian
- Participant
- Posts: 83
- Joined: Tue Dec 19, 2006 8:38 am
Re: Regarding Multi row return
George, there are a few ways to return all matching values from a lookup. The one in question is achieved as follows -
1. Populate your hashed file with the right key(s). Let's call the hashed file MyLkpFile.
2. Now, in the job where the lookup is to be used, use a UV stage with its data source set to localuv (you will not have to enter the user and password).
3. Copy the metadata of MyLkpFile and load it in the columns section of the UV stage.
[Essentially, you are using the UV stage to look into your hashed file. This provides an added advantage that you can use SQL against your hashed file, like you would do with any database].
4. Assuming, you only want to do a multi-row return lookup (and NOT range lookup), you may not require to issue a User defined query, rather just use the default Generated query (in the UV stage General tab).
5. For Table name, use the hashed file name; here MyLkpFile.
6. Now, connect (with a reference link) this UV stage to the Xfmr where the lookup is to be performed.
7. Do the necessary matching logic in the transformer.
8. In the Xfmr properties, right-click on the reference link and select Link properties. This will lead you to the Input tab..and at the bottom, you will find
Reference link with multi row result set. Check this.
I have tried to be as vivid as I linguistically can. Hope this helps.
gateleys[/quote]
Hi Gateleys,
It really helped Gateleys,Thanks.That was a real nice explanation.
Thanks & Regards
George Sebastian
1. Populate your hashed file with the right key(s). Let's call the hashed file MyLkpFile.
2. Now, in the job where the lookup is to be used, use a UV stage with its data source set to localuv (you will not have to enter the user and password).
3. Copy the metadata of MyLkpFile and load it in the columns section of the UV stage.
[Essentially, you are using the UV stage to look into your hashed file. This provides an added advantage that you can use SQL against your hashed file, like you would do with any database].
4. Assuming, you only want to do a multi-row return lookup (and NOT range lookup), you may not require to issue a User defined query, rather just use the default Generated query (in the UV stage General tab).
5. For Table name, use the hashed file name; here MyLkpFile.
6. Now, connect (with a reference link) this UV stage to the Xfmr where the lookup is to be performed.
7. Do the necessary matching logic in the transformer.
8. In the Xfmr properties, right-click on the reference link and select Link properties. This will lead you to the Input tab..and at the bottom, you will find
Reference link with multi row result set. Check this.
I have tried to be as vivid as I linguistically can. Hope this helps.
gateleys[/quote]
Hi Gateleys,
It really helped Gateleys,Thanks.That was a real nice explanation.
Thanks & Regards
George Sebastian