Database Lookup return multiple results

[olgc]

Hello every one,

If a DB2 or ODBC Reference lookup results in multiple results, can all of them be returned? But not just
XfAddress..Email_E_Trns: Reference lookup generated 2 rows. Only the last row was used.

Thanks,

[ray.wurlod]

DB2, no. ODBC, yes. In the ODBC and UV stages only there is a multi-row return property (actually set in the Transfomer stage on the link properties). Look for "multi" in on-line help, or search the forum.

[olgc]

Thanks, I found where is to configure. Only ODBC support multi results lookup.

[ray.wurlod]

And UV.

[Nisusmage]

And UV. ...

I cannot seem to find any help on this. I'm using the ODBC stage for a referenced lookup and it's not returning any rows i.e. o rows. However, when I use the Dynamic RDBMS stage, I get the error that says "Reference Lookup returned x rows. only the last one was used".

Can anyone help here ?

[Nisusmage]

And UV. ...

I cannot seem to find any help on this. I'm using the ODBC stage for a referenced lookup and it's not returning any rows i.e. o rows. However, when I use the Dynamic RDBMS stage, I get the error that says "Reference Lookup returned x rows. only the last one was used".

Can anyone help here ?

I've find the solution:
For a referenced lookup to work with multiple rows you need to do the following:
1) In the properties of the transformer stage you need to check the "reference link with multi row result set" on the reference input link.
2) use the ODBC or a UV stage to pull the referenced data.

I know this has probably all been said before, but I couldn't find a complete solution in one post, so here it is.

[ray.wurlod]

The Help topic is Defining Multirow Lookup for Reference Inputs

It's complete.

[Theenathayalan]

I have a following requirement:

From seq. file, I need to load another seq. file with the below conditions:

1. Input file has one column SolutionNumber.
2. With this solution number, I need to lookup on database to query 15 columns. Lookup query joins 5 or 6 tables. If lookup returns multiple data, I need to write in to reject file with Input solution number. If it returns no rows, then I need to perform another lookup query to get data. Similarly, I have 4 lookup queries to be performed in the order given if no row returns. If multiple row is returned, I need to reject the input solution no.

Hope my req. is clear.

Currently, I am using count(rows) in the lookup query. But the performance is slow.
Eg:

Select solutionno,col1,col2,col3..col15,(select count(1) from tble1,table2mtable3,table4
where cond1,cond2,cond3....and solutionno=:1) rows
from table1,table2mtable3,table4
where cond1,cond2,cond3....and solutionno=:1

In constraint, I am checking, rows=1 or >1 or null.

Looking for alternate solution.
Appreciate any help.

[ray.wurlod]

=We have the following requirement.

You do not hijack someone else's thread, particularly resolved issues. You create a new topic. Near the top of the main part of the page you will find an icon Post new topic that allows you to do so.