I have a query that is not executing as expected when running it
through a federated database. I've narrowed down the part that causes
it to fail and am wondering if anyone knows why it fails, if there's a
better way to do it.

I have 3 DB2 databases all on the same physical server (AIX 5.3 with
DB2 9.5). One is for current data, a second which is a clone of the
first to archive old data, and a third which is a federated database
with nicknames pointing to the tables of the first two database where
we write queries to merge data from both. When executing my query
directly against either of the first two databases, I get the results
expected. The skeleton of the query is something similar to this:

===============================
with t1 as (select columns from myschema.mytable1),
t2 (as select morecolumns from myschema.mytable2)

select T1.co1, T1.col2, T2.col1,
case
when t2.co1 = 20 and exists (select 1 from myschema.mytable3 t3
where t3.col1 = t1.col1) then 'ABC'
else t2.col1
end as mycol
from t1
left join t2 on t1.col1 = t2.col1
===============================

It produces the 173 records I expect. However if substitute the
nicknames the table names and submit it against the federated
database, it produces zero rows. I discovered that if I comment out
the CASE statement, that I get back all 173 rows again. Is there a
restriction against using an EXISTS clause in this fashion?

Any insight would be welcome.

Thanks,
Evan

On 3/16/2010 11:03 AM, esmith2112 wrote:
message.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab