Re-definition of parent table alias inside the sub-query of DML

Article ID: 1013
Last updated: 05 Feb, 2008
Article ID: 1013
Last updated: 05 Feb, 2008
Revision: 1
Views: 4154
Posted: 02 Sep, 1997
by Dean J.
Updated: 05 Feb, 2008
by Dean J.
Problem


I have the following piece of code:
sel << table1;
subSel << table2;
subSel.where( table2[c1] == table1[c1] );
sel.where ( !rwdbExists(subSel) );
This results with no row selected, which is not correct. When I turn on the RWDBTracer, I get
SELECT t0 FROM  table1 t0 WHERE EXISTS( SELECT t1.* FROM  table2 t1, table1 t0 where t1.c1 = t0.c1).
I noticed there is a re-definition of parent table alias inside the sub-query. Could that cause the problem?


Cause


Yes! That re-definition cuts off the relation between the parent query and sub-query, so you got a non-correlated sub-query. The result proved that.


Action


To solve the problem, you need to use an explicit FROM_CLAUSE for the sub-query:
sel << table1;
subSel << table2;
subSel.from ( table2);
subSel.where( table2[c1] == table1[c1] );
sel.where ( !rwdbExists(subSel) );
This discussion also applies to correlated sub-queries in UPDATE and DELETE statements.
This article was:   Helpful | Not helpful
Report an issue
Article ID: 1013
Last updated: 05 Feb, 2008
Revision: 1
Views: 4154
Posted: 02 Sep, 1997 by Dean J.
Updated: 05 Feb, 2008 by Dean J.

Others in this category