multirow lookup

ppalka · Post by **ppalka** » Tue Aug 02, 2005 8:07 am

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

srinagesh · Post by **srinagesh** » Tue Aug 02, 2005 9:15 am

Hi Piotrek,

Try to use select distinct <<lookup field>> from <<lookup table>> as ur SQL query for the lookup table..
In this way you will get only one value.

I hope i have understood your question correctly.

-nagesh

ppalka · Post by **ppalka** » Tue Aug 02, 2005 10:49 am

srinagesh wrote:Hi Piotrek,

I hope i have understood your question correctly.

No, you don't

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.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 02, 2005 6:40 pm

Open Help from the Designer and find the topic Defining Multirow Lookup for Reference Inputs which will explain all.

ppalka · Post by **ppalka** » Wed Aug 03, 2005 4:10 am

ray.wurlod wrote:Open Help from the Designer and find the topic Defining Multirow Lookup for Reference Inputs which will explain all.

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.
PS. When we use multi row result set DS job proceed that set by processing row by row?

ray.wurlod · Post by **ray.wurlod** » Wed Aug 03, 2005 4:09 pm

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).

ppalka · Post by **ppalka** » Thu Aug 04, 2005 3:53 am

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).

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.
Now I have a such statement:
SELECT "id", "id1", "txt" FROM "historyRec" WHERE ("id" = ?) ORDER BY id ASC, id1 ASC;