📄 refactions1.out
字号:
C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 4 |JOE1 |ASHOK |K51 5 |JOE2 |ASHOK |K51 2 |JOHN |ASHOK |K51 3 |ROBIN |ASHOK |K51 13 |DAN |ROGER |K52 12 |JIM |ROGER |K52 11 |ROGER |ROBIN |K52 14 |SAM1 |ROGER |K52 15 |SAM2 |ROGER |K52 16 |GUY |JOHN |K55 6 |HAMID |JOHN |K55 ij> -- "END OF TESTUNIT: 03";-- *************************************************************************-- TESTUNIT : 04-- DESCRIPTION : delete with SQ GB Having SQ on-- : child table-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 04";-- reset to original rowsdelete from db2test.emp;10 rows inserted/updated/deletedij> delete from db2test.dept;2 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.dept where exists ( select max(mgrname) from db2test.secondemp group by dno having dno in (select dno from db2test.emp where mgrname = 'ASHOK')) order by 2, 3;C0 |DNO|DNAME --------------------------3 |K51|CS WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.WARNING 01003: Null values were eliminated from the argument of a column function.2 |K52|OFC 1 |K55|DB ij> delete from db2test.dept where exists ( select max(mgrname) from db2test.secondemp group by dno having dno in (select dno from db2test.emp where mgrname = 'ASHOK'));3 rows inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 ij> -- "END OF TESTUNIT: 04";-- *************************************************************************-- TESTUNIT : 05-- DESCRIPTION : delete with SQ on child table correlated to SQ-- : above -- 7 levels-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 05";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> delete from db2test.dept;0 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> commit;ij> select * from db2test.dept dtop where exists (select * from db2test.secondemp where exists (select dno from db2test.dept D1 where dno = dtop.dno and dno in (select dno from db2test.emp E2 where D1.dno = E2.dno and dno in (select dno from db2test.emp E3 where E2.dno = E3.dno and dno in (select dno from db2test.emp E4 where E3.dno = E4.dno and dno in (select dno from db2test.emp E5 where E4.dno = E5.dno and dno in (select dno from db2test.emp E6 where E5.dno = E6.dno) )))))) order by 2, 3;C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC 1 |K55|DB ij> -- begin RJC950405 (commented out and added ; to the following line as I think we're fixed?)-- -- testcase commented out pending defect fix;-- end RJC950405 delete from db2test.dept where exists (select * from db2test.secondemp where exists (select dno from db2test.dept D1 where dno = db2test.dept.dno and dno in (select dno from db2test.emp E2 where D1.dno = E2.dno and dno in (select dno from db2test.emp E3 where E2.dno = E3.dno and dno in (select dno from db2test.emp E4 where E3.dno = E4.dno and dno in (select dno from db2test.emp E5 where E4.dno = E5.dno and dno in (select dno from db2test.emp E6 where E5.dno = E6.dno) ))))));3 rows inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 ij> -- "END OF TESTUNIT: 05";-- *************************************************************************-- TESTUNIT : 06-- DESCRIPTION : delete with SQ on child table correlated to SQ-- : on second child table, delete cascade on each-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 06";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> delete from db2test.dept;0 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> -- create a second child table like empcreate table db2test.emp2 (c0 int, name char(10) not null primary key, mgrname char(10) references db2test.emp2 on delete cascade, dno char(3) references db2test.dept on delete cascade );0 rows inserted/updated/deletedij> insert into db2test.emp2 select * from db2test.emp;20 rows inserted/updated/deletedij> commit;ij> select * from db2test.dept dtop where exists (select dno from db2test.emp2 E2 where Dtop.dno = E2.dno and dno in (select dno from db2test.emp E3 where E2.dno = E3.dno)) order by 2, 3;C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC 1 |K55|DB ij> delete from db2test.dept where exists (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno and dno in (select dno from db2test.emp E3 where E2.dno = E3.dno));3 rows inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 ij> select * from db2test.emp2 order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> -- "END OF TESTUNIT: 06";-- *************************************************************************-- TESTUNIT : 07-- DESCRIPTION : delete with SQ on child table correlated to SQ-- : on second child table, delete cascade on each-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 07";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> delete from db2test.emp2;0 rows inserted/updated/deletedij> delete from db2test.dept;0 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.emp2 select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> commit;ij> select * from db2test.dept dtop where exists (select dno from db2test.emp2 E2 where dtop.dno = E2.dno) and exists (select dno from db2test.emp E3 where dtop.dno = E3.dno) order by 2,3;C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC 1 |K55|DB ij> delete from db2test.dept where exists (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno) and exists (select dno from db2test.emp E3 where db2test.dept.dno = E3.dno);3 rows inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> select * from db2test.secondemp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------1 |ASHOK |NULL |K51 ij> select * from db2test.emp2 order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------ij> -- "END OF TESTUNIT: 07";-- *************************************************************************-- TESTUNIT : 08-- DESCRIPTION : delete with SQ on child table correlated to SQ-- : on second child table, delete cascade on each-- EXPECTED RESULTS : SQL command should complete successfully-- *************************************************************************-- "START OF TESTUNIT: 08";-- reset to original rowsdelete from db2test.emp;0 rows inserted/updated/deletedij> delete from db2test.emp2;0 rows inserted/updated/deletedij> delete from db2test.secondemp;1 row inserted/updated/deletedij> delete from db2test.dept;0 rows inserted/updated/deletedij> insert into db2test.dept select * from db2test.origdept;3 rows inserted/updated/deletedij> insert into db2test.emp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.secondemp select * from db2test.origemp;20 rows inserted/updated/deletedij> insert into db2test.emp2 select * from db2test.origemp;20 rows inserted/updated/deletedij> select * from db2test.dept dtop where exists (select dno from db2test.emp2 E2 where dtop.dno = E2.dno) or exists (select dno from db2test.emp E3 where dtop.dno = E3.dno) order by 2, 3;C0 |DNO|DNAME --------------------------3 |K51|CS 2 |K52|OFC 1 |K55|DB ij> delete from db2test.dept where exists (select dno from db2test.emp2 E2 where db2test.dept.dno = E2.dno) or exists (select dno from db2test.emp E3 where db2test.dept.dno = E3.dno);3 rows inserted/updated/deletedij> select * from db2test.dept order by dno, dname;C0 |DNO|DNAME --------------------------ij> select * from db2test.emp order by dno, name, mgrname;C0 |NAME |MGRNAME |DNO --------------------------------------⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -