Database Lookup return multiple results
Moderators: chulett, rschirm, roy
Database Lookup return multiple results
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,
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,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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".ray.wurlod wrote:And UV. ...
Can anyone help here ?
~The simpliest solutions are always the best~
~Trick is to understand the complexity to implement simplicity~
~Trick is to understand the complexity to implement simplicity~
I've find the solution:Nisusmage wrote: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".ray.wurlod wrote:And UV. ...
Can anyone help here ?
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.
~The simpliest solutions are always the best~
~Trick is to understand the complexity to implement simplicity~
~Trick is to understand the complexity to implement simplicity~
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 15
- Joined: Fri Jul 06, 2007 4:48 pm
Multiple row/no row handling
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
=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.
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.
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.