Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 89030 invoked from network); 18 Apr 2005 05:22:08 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 18 Apr 2005 05:22:08 -0000 Received: (qmail 44478 invoked by uid 500); 18 Apr 2005 05:22:07 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 44226 invoked by uid 500); 18 Apr 2005 05:22:06 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 44210 invoked by uid 99); 18 Apr 2005 05:22:06 -0000 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (hermes.apache.org: local policy) Received: from nwkea-mail-1.sun.com (HELO nwkea-mail-1.sun.com) (192.18.42.13) by apache.org (qpsmtpd/0.28) with ESMTP; Sun, 17 Apr 2005 22:22:05 -0700 Received: from phys-biff-2 ([129.158.227.37]) by nwkea-mail-1.sun.com (8.12.10/8.12.9) with ESMTP id j3I5M0jG006730 for ; Sun, 17 Apr 2005 22:22:01 -0700 (PDT) Received: from conversion-daemon.biff-mail1.india.sun.com by biff-mail1.india.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) id <0IF400K01MRSFR@biff-mail1.india.sun.com> (original mail from Shreyas.Kaushik@Sun.COM) for derby-dev@db.apache.org; Mon, 18 Apr 2005 10:52:00 +0530 (IST) Received: from [192.168.1.100] (vpn-129-150-156-2.India.Sun.COM [129.150.156.2]) by biff-mail1.india.sun.com (iPlanet Messaging Server 5.2 HotFix 1.24 (built Dec 19 2003)) with ESMTP id <0IF400EMKMWM2K@biff-mail1.india.sun.com> for derby-dev@db.apache.org; Mon, 18 Apr 2005 10:52:00 +0530 (IST) Date: Mon, 18 Apr 2005 10:52:35 +0530 From: Shreyas Kaushik Subject: Re: [PATCH] Derby-156 In-reply-to: To: Derby Development Message-id: <4263441B.9020908@sun.com> MIME-version: 1.0 Content-type: multipart/mixed; boundary="Boundary_(ID_DO+J9VXgYVwLen9NMNSjqA)" X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) References: <42632E66.3020403@sun.com> X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. --Boundary_(ID_DO+J9VXgYVwLen9NMNSjqA) Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7BIT Hi Mamta, Done, test case added in the latest patch attached. ~ Shreyas Mamta Satoor wrote: >Hi Shreyas, > >Unless I missed it, I don't see a test like the one in the Jira entry >for this bug >DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value'; >This test case attempts to use the correlation name in the where clause. > >Mamta > >On 4/17/05, Shreyas Kaushik wrote: > > >>I have attched the patch for this. I have also added test cases that >>test this feature. Also I have made some changes to the refActions1 test >>case. >>I ran the derbyall test suite without any failures. >> >>thanks >>Shreyas >> >> >>Index: java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java >>=================================================================== >>--- java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (revision 161449) >>+++ java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (working copy) >>@@ -241,6 +241,13 @@ >> resultColumnList = new ResultColumnList(); >> >> FromBaseTable fbt = getResultColumnList(resultColumnList); >>+ >>+ if(targetTable instanceof FromBaseTable) { >>+ String correlationName; >>+ correlationName = ((FromBaseTable)targetTable).correlationName; >>+ if(correlationName != null) >>+ fbt.correlationName = correlationName; >>+ } >> >> readColsBitSet = getReadMap(dataDictionary, >> targetTableDescriptor); >>Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj >>=================================================================== >>--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision 161449) >>+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy) >>@@ -2565,16 +2565,22 @@ >> QueryTreeNode retval; >> Properties targetProperties = null; >> Token whereToken = null; >>+ String correlationName = null; >>+ Object []objArr = null; >>} >>{ >> LOOKAHEAD( { fromNewInvocationFollows() } ) >> javaToSQLNode = newInvocation() >>+ { >>+ objArr = optionalTableClauses(); >>+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >>+ } >> [ whereToken = whereClause = whereClause(whereToken) ] >> { >> fromTable = (FromTable) nodeFactory.getNode( >> C_NodeTypes.FROM_VTI, >> javaToSQLNode.getJavaValueNode(), >>- (String) null, >>+ correlationName, >> null, >> (Properties) null, >> getContextManager()); >>@@ -2582,7 +2588,11 @@ >> return getDeleteNode(fromTable, tableName, whereClause); >> } >>| >>- tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >>+ tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) >>+ { >>+ objArr = optionalTableClauses(); >>+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; >>+ } >> [targetProperties = propertyList() ] >> [ >> whereToken = >>@@ -2614,7 +2624,7 @@ >> fromTable = (FromTable) nodeFactory.getNode( >> C_NodeTypes.FROM_BASE_TABLE, >> tableName, >>- null, >>+ correlationName, >> ReuseFactory.getInteger( >> FromBaseTable.DELETE), >> null, >>Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql >>=================================================================== >>--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) >>+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) >>@@ -0,0 +1,23 @@ >>+-- This tests the delete functionality with correlation name >>+ >>+create table corrDelete(ival int, cval varchar(10)); >>+insert into corrDelete values(1,'test1'); >>+insert into corrDelete values(2,'test2'); >>+insert into corrDelete values(3,'test3'); >>+insert into corrDelete values(4,'test4'); >>+insert into corrDelete values(5,'test5'); >>+insert into corrDelete values(6,'test6'); >>+ >>+select * from corrDelete; >>+ >>+delete from corrDelete d where ival=3; >>+ >>+select * from corrDelete; >>+ >>+delete from corrDelete as d where ival=5; >>+ >>+select * from corrDelete; >>+ >>+delete from corrDelete d; >>+ >>+select * from corrDelete; >>Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql >>=================================================================== >>--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (revision 161449) >>+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (working copy) >>@@ -719,6 +719,14 @@ >>select * from db2test.emp13 order by dno, name, mgrname; >>select * from db2test.emp14 order by dno, name, mgrname; >>select * from db2test.emp15 order by dno, name, mgrname; >>+delete from db2test.dept d where >>+ dno in (select dno from db2test.emp e where >>+ e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>+ e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>+ e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>+ e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>+ e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>+ e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>-- "END OF TESTUNIT: 11"; >> >>@@ -2306,14 +2314,6 @@ >> e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >> e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))) >> order by 2, 3; >>-delete from db2test.dept d where >>- dno in (select dno from db2test.emp e where >>- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>- e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>select * from db2test.dept order by dno, dname; >>select * from db2test.emp order by dno, name, mgrname; >>select * from db2test.secondemp order by dno, name, mgrname; >>Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant >>=================================================================== >>--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (revision 161449) >>+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (working copy) >>@@ -209,3 +209,4 @@ >>wisconsin_app.properties >>wisconsin_derby.properties >>wisconsin_sed.properties >>+corrDelete.sql >>Index: java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out >>=================================================================== >>--- java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) >>+++ java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) >>@@ -0,0 +1,49 @@ >>+ij> -- This tests the delete functionality with correlation name >>+create table corrDelete(ival int, cval varchar(10)); >>+0 rows inserted/updated/deleted >>+ij> insert into corrDelete values(1,'test1'); >>+1 row inserted/updated/deleted >>+ij> insert into corrDelete values(2,'test2'); >>+1 row inserted/updated/deleted >>+ij> insert into corrDelete values(3,'test3'); >>+1 row inserted/updated/deleted >>+ij> insert into corrDelete values(4,'test4'); >>+1 row inserted/updated/deleted >>+ij> insert into corrDelete values(5,'test5'); >>+1 row inserted/updated/deleted >>+ij> insert into corrDelete values(6,'test6'); >>+1 row inserted/updated/deleted >>+ij> select * from corrDelete; >>+IVAL |CVAL >>+---------------------- >>+1 |test1 >>+2 |test2 >>+3 |test3 >>+4 |test4 >>+5 |test5 >>+6 |test6 >>+ij> delete from corrDelete d where ival=3; >>+1 row inserted/updated/deleted >>+ij> select * from corrDelete; >>+IVAL |CVAL >>+---------------------- >>+1 |test1 >>+2 |test2 >>+4 |test4 >>+5 |test5 >>+6 |test6 >>+ij> delete from corrDelete as d where ival=5; >>+1 row inserted/updated/deleted >>+ij> select * from corrDelete; >>+IVAL |CVAL >>+---------------------- >>+1 |test1 >>+2 |test2 >>+4 |test4 >>+6 |test6 >>+ij> delete from corrDelete d; >>+4 rows inserted/updated/deleted >>+ij> select * from corrDelete; >>+IVAL |CVAL >>+---------------------- >>+ij> >>Index: java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out >>=================================================================== >>--- java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (revision 161449) >>+++ java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (working copy) >>@@ -1733,6 +1733,15 @@ >>5 |JOE2 |ASHOK |K51 >>2 |JOHN |ASHOK |K51 >>3 |ROBIN |ASHOK |K51 >>+ij> delete from db2test.dept d where >>+ dno in (select dno from db2test.emp e where >>+ e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>+ e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>+ e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>+ e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>+ e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>+ e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>+0 rows inserted/updated/deleted >>ij> -- "END OF TESTUNIT: 11"; >>-- ************************************************************************* >>-- TESTUNIT : 12 >>@@ -5998,15 +6007,6 @@ >>-------------------------- >>2 |K52|OFC >>1 |K55|DB >>-ij> delete from db2test.dept d where >>- dno in (select dno from db2test.emp e where >>- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where >>- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where >>- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where >>- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where >>- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where >>- e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); >>-ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26. >>ij> select * from db2test.dept order by dno, dname; >>C0 |DNO|DNAME >>-------------------------- >>@@ -7585,7 +7585,7 @@ >> where e3.name = e2.mgrname group by dno having >> e2.dno in (select dno from db2test.emp e1 >> where e1.name = e.mgrname and e1.mgrname = 'JOHN'))))); >>-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >>+ERROR 42X04: Column 'E5.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E5.NAME' is not a column in the target table. >>ij> select * from db2test.emp order by dno, name, mgrname; >>C0 |NAME |MGRNAME |DNO >>-------------------------------------- >>@@ -7753,7 +7753,7 @@ >> where e.name = e2.mgrname group by dno having >> e2.dno in (select dno from db2test.emp e1 >> where e.mgrname = 'JOHN'))))); >>-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. >>+ERROR 42X04: Column 'E.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E.NAME' is not a column in the target table. >>ij> select * from db2test.emp order by dno, name, mgrname; >>C0 |NAME |MGRNAME |DNO >>-------------------------------------- >> >> >> >> >> --Boundary_(ID_DO+J9VXgYVwLen9NMNSjqA) Content-type: text/plain; name=Derby-156.patch Content-transfer-encoding: 7BIT Content-disposition: inline; filename=Derby-156.patch Index: java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java =================================================================== --- java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (revision 161449) +++ java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (working copy) @@ -241,6 +241,13 @@ resultColumnList = new ResultColumnList(); FromBaseTable fbt = getResultColumnList(resultColumnList); + + if(targetTable instanceof FromBaseTable) { + String correlationName; + correlationName = ((FromBaseTable)targetTable).correlationName; + if(correlationName != null) + fbt.correlationName = correlationName; + } readColsBitSet = getReadMap(dataDictionary, targetTableDescriptor); Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj =================================================================== --- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision 161449) +++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy) @@ -2565,16 +2565,22 @@ QueryTreeNode retval; Properties targetProperties = null; Token whereToken = null; + String correlationName = null; + Object []objArr = null; } { LOOKAHEAD( { fromNewInvocationFollows() } ) javaToSQLNode = newInvocation() + { + objArr = optionalTableClauses(); + correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; + } [ whereToken = whereClause = whereClause(whereToken) ] { fromTable = (FromTable) nodeFactory.getNode( C_NodeTypes.FROM_VTI, javaToSQLNode.getJavaValueNode(), - (String) null, + correlationName, null, (Properties) null, getContextManager()); @@ -2582,7 +2588,11 @@ return getDeleteNode(fromTable, tableName, whereClause); } | - tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) + tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) + { + objArr = optionalTableClauses(); + correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME]; + } [targetProperties = propertyList() ] [ whereToken = @@ -2614,7 +2624,7 @@ fromTable = (FromTable) nodeFactory.getNode( C_NodeTypes.FROM_BASE_TABLE, tableName, - null, + correlationName, ReuseFactory.getInteger( FromBaseTable.DELETE), null, Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/corrDelete.sql (revision 0) @@ -0,0 +1,23 @@ +-- This tests the delete functionality with correlation name + +create table corrDelete(ival int, cval varchar(10)); +insert into corrDelete values(1,'test1'); +insert into corrDelete values(2,'test2'); +insert into corrDelete values(3,'test3'); +insert into corrDelete values(4,'test4'); +insert into corrDelete values(5,'test5'); +insert into corrDelete values(6,'test6'); + +select * from corrDelete; + +delete from corrDelete d where ival=3; + +select * from corrDelete; + +delete from corrDelete as d where d.ival=5; + +select * from corrDelete; + +delete from corrDelete d; + +select * from corrDelete; Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (revision 161449) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (working copy) @@ -719,6 +719,14 @@ select * from db2test.emp13 order by dno, name, mgrname; select * from db2test.emp14 order by dno, name, mgrname; select * from db2test.emp15 order by dno, name, mgrname; +delete from db2test.dept d where + dno in (select dno from db2test.emp e where + e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where + e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where + e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where + e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where + e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where + e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); -- "END OF TESTUNIT: 11"; @@ -2306,14 +2314,6 @@ e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))) order by 2, 3; -delete from db2test.dept d where - dno in (select dno from db2test.emp e where - e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where - e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where - e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where - e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where - e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where - e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); select * from db2test.dept order by dno, dname; select * from db2test.emp order by dno, name, mgrname; select * from db2test.secondemp order by dno, name, mgrname; Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (revision 161449) +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant (working copy) @@ -209,3 +209,4 @@ wisconsin_app.properties wisconsin_derby.properties wisconsin_sed.properties +corrDelete.sql Index: java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) +++ java/testing/org/apache/derbyTesting/functionTests/master/corrDelete.out (revision 0) @@ -0,0 +1,49 @@ +ij> -- This tests the delete functionality with correlation name +create table corrDelete(ival int, cval varchar(10)); +0 rows inserted/updated/deleted +ij> insert into corrDelete values(1,'test1'); +1 row inserted/updated/deleted +ij> insert into corrDelete values(2,'test2'); +1 row inserted/updated/deleted +ij> insert into corrDelete values(3,'test3'); +1 row inserted/updated/deleted +ij> insert into corrDelete values(4,'test4'); +1 row inserted/updated/deleted +ij> insert into corrDelete values(5,'test5'); +1 row inserted/updated/deleted +ij> insert into corrDelete values(6,'test6'); +1 row inserted/updated/deleted +ij> select * from corrDelete; +IVAL |CVAL +---------------------- +1 |test1 +2 |test2 +3 |test3 +4 |test4 +5 |test5 +6 |test6 +ij> delete from corrDelete d where ival=3; +1 row inserted/updated/deleted +ij> select * from corrDelete; +IVAL |CVAL +---------------------- +1 |test1 +2 |test2 +4 |test4 +5 |test5 +6 |test6 +ij> delete from corrDelete as d where d.ival=5; +1 row inserted/updated/deleted +ij> select * from corrDelete; +IVAL |CVAL +---------------------- +1 |test1 +2 |test2 +4 |test4 +6 |test6 +ij> delete from corrDelete d; +4 rows inserted/updated/deleted +ij> select * from corrDelete; +IVAL |CVAL +---------------------- +ij> Index: java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out =================================================================== --- java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (revision 161449) +++ java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (working copy) @@ -1733,6 +1733,15 @@ 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 +ij> delete from db2test.dept d where + dno in (select dno from db2test.emp e where + e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where + e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where + e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where + e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where + e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where + e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); +0 rows inserted/updated/deleted ij> -- "END OF TESTUNIT: 11"; -- ************************************************************************* -- TESTUNIT : 12 @@ -5998,15 +6007,6 @@ -------------------------- 2 |K52|OFC 1 |K55|DB -ij> delete from db2test.dept d where - dno in (select dno from db2test.emp e where - e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where - e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where - e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where - e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where - e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where - e6.dno = e5.dno and e6.dno in ('K55', 'K52'))))))); -ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26. ij> select * from db2test.dept order by dno, dname; C0 |DNO|DNAME -------------------------- @@ -7585,7 +7585,7 @@ where e3.name = e2.mgrname group by dno having e2.dno in (select dno from db2test.emp e1 where e1.name = e.mgrname and e1.mgrname = 'JOHN'))))); -ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. +ERROR 42X04: Column 'E5.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E5.NAME' is not a column in the target table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- @@ -7753,7 +7753,7 @@ where e.name = e2.mgrname group by dno having e2.dno in (select dno from db2test.emp e1 where e.mgrname = 'JOHN'))))); -ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25. +ERROR 42X04: Column 'E.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'E.NAME' is not a column in the target table. ij> select * from db2test.emp order by dno, name, mgrname; C0 |NAME |MGRNAME |DNO -------------------------------------- --Boundary_(ID_DO+J9VXgYVwLen9NMNSjqA)--