I have in my input a reference column. I need to connect that reference field with records from another file. And in that file I will have many referenced records, because id is in format reference;n. So there could be many referenced records: reference;1, reference;2, reference;3, etc. But I don't know the number of referenced records.
How would you advise me to solve that problem?
Thanks in advance.
Best regards,
Piotrek
multirow lookup
Moderators: chulett, rschirm, roy
No, you don'tsrinagesh wrote:Hi Piotrek,
I hope i have understood your question correctly.
In lookup table there are no duplicated records. But for every source record, there are n (variuos number) records in lookup table, but each record has a different id: "refId;nr", refId - a lookup key, nr - a number of lookup record in a sequence.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, I have just read that chapter some time ago. But I still don't know how to solve my problem, because my foreign key is not the same as key in reference table. It is concatenated of foreign key, ';' sign and a sequence number. So the problem is to make a proper join operation.ray.wurlod wrote:Open Help from the Designer and find the topic Defining Multirow Lookup for Reference Inputs which will explain all.
PS. When we use multi row result set DS job proceed that set by processing row by row?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use user-defined SQL in the ODBC stage. If the lookup delivers N rows for a single lookup there will be N rows delivered to the Transformer stage's output (assuming no constraint).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I solved my problem by using UV stage. But before I put my data in UV table I need to cut the last part of id column ( before: id;n, after: id) in transformer stage. And I am wondering if I can solve the problem without using transformer? I tried to use LIKE statement in user-defined sql query, but I didn't figure how to do that.ray.wurlod wrote:Use user-defined SQL in the ODBC stage. If the lookup delivers N rows for a single lookup there will be N rows delivered to the Transformer stage's output (assuming no constraint).
Now I have a such statement:
SELECT "id", "id1", "txt" FROM "historyRec" WHERE ("id" = ?) ORDER BY id ASC, id1 ASC;