Using WebSphere Adapter for JDBC to generate query business objects from nested select queries is not supported

Resolving The Problem

The WebSphere Adapter for JDBC cannot generate query business objects from SQL select statements that contain nested select statements (statements containing a "where" clause). For example, a select statement with the following format is not supported.

select * from TABLE1 T1, (select col1, col2 from TABLE2 where col3 = 'VAL') T2 where T1.col1 = T2.col1 and T1.col2 = 'VAL'

For this type of statement, the adapter generates an invalid value for the jdbcwhereclause attribute in the query business object, causing the outbound operation to fail while processing this query business object at run time.

As a workaround, if you want to use a query like the one described above, you can create a view for the nested query and then use the view in your main query. For example, you can create a view for the nested query VIEW1 (select col1, col2 from TABLE2 where col3 = 'VAL' ), for instance, and then use the main query to generate a query business object. The updated query would be:

select * from TABLE1 T1, VIEW1 T2 where T1.col1 = T2.col1 and T1.col2 = 'VAL'

Alternatively, because the adapter supports discovery for stored procedures that return cursors, you can create a stored procedure in the database that returns a cursor for queries like the previous example. The adapter can also process these stored procedures at run time, which provides more flexibility in terms of SQL queries that you want to return.