Reject multiple rows from lookup

[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]

You could consider tuning the database query, perhaps with an EXISTS clause.

Your DataStage job needs two Transformer stages. One to perform this complex lookup via ODBC, and to determine whether zero, one or more than one row was returned (three separate outputs).

Downstream on these outputs you can do whatever you need to do. On the "zero rows returned" output is where the second Transformer stage goes. You did not specify an action if precisely one row is returned.