Reject multiple rows from lookup
Posted: Sun Jul 22, 2007 8:35 pm
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.