I am using output of SQL Server subquery in DB2 query IN clause.
I have a hurdle, if subquery found no record, then what it will return?

Beacuse the output of this query will be input of IN clause in DB2 query, what if nothing is returned by the SQLServer query?



Example:

SQL Server SubQuery
select CLNT_ID from CLNT_ACS where INTN_ID=12
Output:
1234

DB2 Query
SELECT CLNT_ID, CLNT_NM from CLNT WHERE CLNT_ID IN (1234) ORDER BY CLNT_NM

How do you tie both queries together? Do you use DB2's federated capabilities or do you build the DB2 query in some sort of application? In the first case (federation), you don't have to worry about this - just nest the query over the nickname into the IN predicate. In the second case, you have to detect this situation in your application and guarantee that DB2 will not see an empty IN-list in the query. Either you do not execute the query at all or you provide a dummy element.