0

Why this simple query works fine in oracle but doesn't work in DB2:

select * 
from 
sysibm.dual d1 
left join sysibm.dual d2 on 1=1 and exists (select 1 from sysibm.dual)

Moving subquery-involving condition to where clause may help, but that will restrain outer join into inner.

user1741227
  • 9
  • 1
  • 2

2 Answers2

1

When I try to run the query you have, I get a -338 error, which according to Information Center (see link), there are the following restrictions on the ON clause:

An ON clause associated with a JOIN operator or in a MERGE statement is not valid for one of the following reasons.

* The ON clause cannot include any subqueries.
* Column references in an ON clause must only reference columns
  of tables that are in the scope of the ON clause.
* Scalar fullselects are not allowed in the expressions of an ON clause.
* A function referenced in an ON clause of a full outer join 
  must be deterministic and have no external action.
* A dereference operation (->) cannot be used.
* A SQL function or SQL method cannot be used.
* The ON clause cannot include an XMLQUERY or XMLEXISTS expression.

I'm not sure if it's possible with your query, but do you think perhaps you could re-write something like this:

select * 
from 
sysibm.dual d1 
left join (
    SELECT dl.*,
    CASE WHEN EXISTS (SELECT 1 FROM sysibm.dual)
         THEN 1
         ELSE 0
    END AS jn
    FROM sysibm.dual dl
) D2
  on 1=1 and 1=d2.jn
bhamby
  • 15,112
  • 1
  • 45
  • 66
  • I could rewrite it but as I think joining a subquery as table could lead to full-scans. I have abstract examples:`1)select * from accounts a join operations o on (o.acc_id=a.acc_id and exists (select null from operation where acc_id=o.acc_id having max(ope_date)=o.ope_date)) 2)select * from accounts a join operations o on (o.acc_id=a.acc_id) join (select max(ope_date) as ope_date,acc_id from operations group by acc_id) latestope on (latestope.acc_id=o.acc_id and latestope.ope_date=o.ope_date)` Second one causes full scan as I think – user1741227 Jan 26 '13 at 19:44
  • The second query could be written like `SELECT * FROM accounts a JOIN operations o ON o.acc_id = a.acc_id WHERE o.ope_date = ( SELECT MAX(ope_date) FROM operations d WHERE o.acc_id = d.acc_id )` If you have an index that includes `o.acc_id` and `o.ope_date`, then DB2 will use the index to resolve the `MAX()`. We have a similar table here at `$work`, and a similar query runs very fast. – bhamby Jan 28 '13 at 14:45
0

This works in DB2 V10.1! No fixpack installed.

Bjallemad
  • 1