| >> |
| >>?section create_tables |
| >>create table t005t01(eno int not null , |
| +> dno largeint, |
| +> primary key(eno) ); |
| |
| --- SQL operation complete. |
| >> |
| >>create table t005t02(dno largeint not null , |
| +> dname char(20) not null, |
| +> primary key (dno) ); |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>create table t005t03 |
| +>( |
| +> seqno integer not null not droppable, |
| +> smin1 smallint default null, |
| +> pict1 pic s9(13)v9(5) default null, |
| +> primary key (seqno) |
| +>) ; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t005t04 |
| +>( |
| +> seqno integer not null not droppable, |
| +> smin1 smallint default null, |
| +> pict1 pic s9(13)v9(5) default null, |
| +> primary key (seqno) |
| +>) ; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>create table t005t05(seqno integer not null primary key ) ; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t005_ex ( |
| +> e1 decimal(10,0) not null, |
| +> e2 decimal(10,0) not null, |
| +> e3 decimal(10,0) not null |
| +> ) no partition; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>create table t005_fx ( |
| +> f1 decimal(10,0) not null, |
| +> f2 decimal(10,0) not null, |
| +> f3 decimal(10,0) not null |
| +>) no partition; |
| |
| --- SQL operation complete. |
| >> |
| >> |
| >>create table t005_gx ( |
| +> g1 decimal(10,0) default null, |
| +> g2 decimal(10,0) default null |
| +>) no partition; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t005_hx ( |
| +> h1 decimal(10,0) not null, |
| +> h2 decimal(10,0) not null, |
| +> h3 decimal(10,0) not null |
| +>) no partition; |
| |
| --- SQL operation complete. |
| >> |
| >>create table witht1 (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>create table witht2 (c1 int, c2 int); |
| |
| --- SQL operation complete. |
| >>?section populate_tables |
| >>-- Populate t005t01 |
| >>insert into t005t01 values (30, 33); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t01 values (3, 33); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t01 values (4, 44); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t01 values (5, 55); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t01 values (6, 66); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- Populate t005t02 |
| >>insert into t005t02 values (33, 'Sales'), |
| +> (44, 'Marketing'), |
| +> (55, 'Production'), |
| +> (77, 'R&D'); |
| |
| --- 4 row(s) inserted. |
| >> |
| >>insert into t005_ex (e1, e2, e3) values(1,1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,1,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,1,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,2,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,2,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,2,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,3,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,3,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_ex (e1, e2, e3) values(1,3,3); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t005_fx select * from t005_ex; |
| |
| --- 9 row(s) inserted. |
| >> |
| >>insert into t005_gx values(1,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_gx values(2,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_gx values(3,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_gx values(4,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t005_hx values(1,1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,1,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,1,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,1,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,2,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,2,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,2,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,3,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,3,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(1,3,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,1,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,1,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,1,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,2,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,2,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005_hx values(2,2,3); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into witht1 values(1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht1 values(2,2); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht1 values(3,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht1 values(4,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht1 values(5,5); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht2 values(3,3); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht2 values(4,4); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht2 values(5,5); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht2 values(6,6); |
| |
| --- 1 row(s) inserted. |
| >>insert into witht2 values(7,7); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>?section prep |
| >>control query default query_cache '0'; |
| |
| --- SQL operation complete. |
| >>control query default comp_bool_199 'on'; |
| |
| --- SQL operation complete. |
| >> |
| >>?section positive_tests |
| >> |
| >>-- FOJ1 - Natural Full Outer Join |
| >>-- t005t01 FOJ t005t02 |
| >>select * from t005t01 natural full outer join t005t02 order by 1,2 DESC; |
| |
| DNO ENO DNAME |
| -------------------- ----------- -------------------- |
| |
| 33 30 Sales |
| 33 3 Sales |
| 44 4 Marketing |
| 55 5 Production |
| 66 6 ? |
| 77 ? R&D |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- FOJ2 - Natural Full Outer Join |
| >>-- t005t02 FOJ t005t01 |
| >>select * from t005t02 natural full outer join t005t01 order by 1,3; |
| |
| DNO DNAME ENO |
| -------------------- -------------------- ----------- |
| |
| 33 Sales 3 |
| 33 Sales 30 |
| 44 Marketing 4 |
| 55 Production 5 |
| 66 ? 6 |
| 77 R&D ? |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- FOJ3 - Use ON clause - non equijoin predicate |
| >>-- t005t02 FOJ t005t01 |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = 33 order by 1,3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| 44 Marketing 3 33 |
| 44 Marketing 30 33 |
| 55 Production 3 33 |
| 55 Production 30 33 |
| 77 R&D 3 33 |
| 77 R&D 30 33 |
| ? ? 4 44 |
| ? ? 5 55 |
| ? ? 6 66 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- FOJ4 - Use ON clause - non equijoin predicate |
| >>-- t005t01 FOJ t005t02 |
| >>select * from t005t01 full outer join t005t02 on t005t02.dno = 33 order by 1,4; |
| |
| ENO DNO DNO DNAME |
| ----------- -------------------- -------------------- -------------------- |
| |
| 3 33 33 Sales |
| 4 44 33 Sales |
| 5 55 33 Sales |
| 6 66 33 Sales |
| 30 33 33 Sales |
| ? ? 44 Marketing |
| ? ? 55 Production |
| ? ? 77 R&D |
| |
| --- 8 row(s) selected. |
| >> |
| >> |
| >>-- FOJ5 - Use ON clause - equijoin predicate |
| >>-- t005t02 FOJ t005t01 |
| >>select * from t005t02 full outer join t005t01 on t005t02.dno = t005t01.dno order by 1,3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| 44 Marketing 4 44 |
| 55 Production 5 55 |
| 77 R&D ? ? |
| ? ? 6 66 |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- FOJ6 - Use ON clause - equijoin predicate |
| >>-- t005t01 FOJ t005t02 |
| >>select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno order by 1; |
| |
| ENO DNO DNO DNAME |
| ----------- -------------------- -------------------- -------------------- |
| |
| 3 33 33 Sales |
| 4 44 44 Marketing |
| 5 55 55 Production |
| 6 66 ? ? |
| 30 33 33 Sales |
| ? ? 77 R&D |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- FOJ7 - Use WHERE clause on Full Outer |
| >>-- Join t005t02 FOJ t005t01 |
| >>-- WHERE clause on t005t02 and the column is part of a join column |
| >>-- Must Convert FOJ to LJ - TBD - Hema |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno |
| +> where t005t02.dno = 33 order by 3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- FOJ8 - Use WHERE clause on Full Outer |
| >>-- Join t005t02 FOJ t005t01 |
| >>-- WHERE clause on t005t01 and the column is part of a join column |
| >>-- Must Convert FOJ to RJ - TBD - Hema |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno |
| +> where t005t01.dno = 33 order by 3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>-- FOJ9 - Use AND in ON clause on Full Outer |
| >>-- Join t005t02 FOJ t005t01 |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno |
| +> AND t005t01.dno = 33 order by 1,3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| 44 Marketing ? ? |
| 55 Production ? ? |
| 77 R&D ? ? |
| ? ? 4 44 |
| ? ? 5 55 |
| ? ? 6 66 |
| |
| --- 8 row(s) selected. |
| >> |
| >>-- FOJ10 - Use AND in ON clause on Full Outer |
| >>-- Join t005t01 FOJ t005t02 |
| >>select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno |
| +> AND t005t01.dno = 33 order by 1,4; |
| |
| ENO DNO DNO DNAME |
| ----------- -------------------- -------------------- -------------------- |
| |
| 3 33 33 Sales |
| 4 44 ? ? |
| 5 55 ? ? |
| 6 66 ? ? |
| 30 33 33 Sales |
| ? ? 44 Marketing |
| ? ? 55 Production |
| ? ? 77 R&D |
| |
| --- 8 row(s) selected. |
| >> |
| >> |
| >>-- FOJ11 - Use OR in ON clause on Full Outer |
| >>-- Join t005t02 FOJ t005t01 |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno |
| +> OR t005t01.dno = 33 order by 1,3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| 44 Marketing 3 33 |
| 44 Marketing 4 44 |
| 44 Marketing 30 33 |
| 55 Production 3 33 |
| 55 Production 5 55 |
| 55 Production 30 33 |
| 77 R&D 3 33 |
| 77 R&D 30 33 |
| ? ? 6 66 |
| |
| --- 11 row(s) selected. |
| >> |
| >>-- FOJ12 - Use OR in ON clause on Full Outer |
| >>-- Join t005t01 FOJ t005t02 |
| >>select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno |
| +> OR t005t01.dno = 33 order by 1, 3; |
| |
| ENO DNO DNO DNAME |
| ----------- -------------------- -------------------- -------------------- |
| |
| 3 33 33 Sales |
| 3 33 44 Marketing |
| 3 33 55 Production |
| 3 33 77 R&D |
| 4 44 44 Marketing |
| 5 55 55 Production |
| 6 66 ? ? |
| 30 33 33 Sales |
| 30 33 44 Marketing |
| 30 33 55 Production |
| 30 33 77 R&D |
| |
| --- 11 row(s) selected. |
| >> |
| >> |
| >>?section DERIVED_TABLE |
| >>-- FOJ13 - use derived table syntax |
| >>-- Join t005t02 FOJ t005t01 |
| >>-- WHERE clause |
| >>select * from (select * from t005t02 full outer join t005t01 |
| +> on t005t01.dno = t005t02.dno) as T(a,b,c,d) where T.a = 33 order by 3; |
| |
| A B C D |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 33 Sales 30 33 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- FOJ13 - use derived table syntax |
| >>-- Join t005t02 FOJ t005t01 |
| >>select * from (select * from t005t02 full outer join |
| +> t005t01 on t005t01.dno = t005t02.dno) as T(a,b,c,d) order by 3; |
| |
| A B C D |
| -------------------- -------------------- ----------- -------------------- |
| |
| 33 Sales 3 33 |
| 44 Marketing 4 44 |
| 55 Production 5 55 |
| ? ? 6 66 |
| 33 Sales 30 33 |
| 77 R&D ? ? |
| |
| --- 6 row(s) selected. |
| >> |
| >> |
| >>?section predicates |
| >>delete from t005t03; |
| |
| --- 0 row(s) deleted. |
| >>delete from t005t04; |
| |
| --- 0 row(s) deleted. |
| >> |
| >>insert INTO t005t03 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (2, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 (seqno) values (3); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (5, 32767,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (6,7892,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (7,2834,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (8,123,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >> |
| >>insert INTO t005t04 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (2, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (3,24923,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (5,25065,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (6,7892,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (12,9374,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- FOJ14 - selection predicate on left table columns |
| >>-- Join t005t03 FOJ t005t04 |
| >>select a.seqno, a.smin1, b.seqno, b.smin1 FROM |
| +> t005t03 a |
| +> FULL OUTER JOIN |
| +> t005t04 b ON a.seqno = b.seqno |
| +> where (a.seqno > 3 and a.smin1 < 123) or a.seqno < 5 |
| +>ORDER BY a.seqno, b.seqno, a.smin1, b.smin1 ; |
| |
| SEQNO SMIN1 SEQNO SMIN1 |
| ----------- ------ ----------- ------ |
| |
| 1 1 1 1 |
| 2 1 2 1 |
| 3 ? 3 24923 |
| 4 -32768 4 -32768 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- FOJ15 - selection predicate on right table columns |
| >>-- Join t005t03 FOJ t005t04 |
| >>select a.seqno, a.smin1, b.seqno, b.smin1 FROM t005t03 a |
| +> FULL OUTER JOIN |
| +>t005t04 b ON a.seqno = b.seqno |
| +>where (b.seqno > 3 and b.smin1 < 123) or b.seqno = 2 order by a.seqno; |
| |
| SEQNO SMIN1 SEQNO SMIN1 |
| ----------- ------ ----------- ------ |
| |
| 2 1 2 1 |
| 4 -32768 4 -32768 |
| |
| --- 2 row(s) selected. |
| >> |
| >> |
| >>delete from t005t03; |
| |
| --- 8 row(s) deleted. |
| >>delete from t005t04; |
| |
| --- 7 row(s) deleted. |
| >> |
| >>insert INTO t005t04 values(0, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(1,1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 (seqno) values (3); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(-2147483648, -32768,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(2147483647, 32767,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(293847923, 7892,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(450,2834,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(789,123,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >> |
| >>insert INTO t005t04 values(2,1,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- FOJ15 - selection predicate on left and right table columns |
| >>-- Join t005t03 FOJ t005t04 |
| >>select a.seqno, a.smin1, b.seqno, b.smin1 FROM t005t03 a |
| +> FULL OUTER JOIN |
| +>t005t04 b ON a.seqno = b.seqno |
| +>where (a.seqno > 3 and a.seqno < 123) or b.seqno = 2 order by a.seqno; |
| |
| SEQNO SMIN1 SEQNO SMIN1 |
| ----------- ------ ----------- ------ |
| |
| ? ? 2 1 |
| |
| --- 1 row(s) selected. |
| >> |
| >>?section PIC_Datatype |
| >>delete from t005t03; |
| |
| --- 0 row(s) deleted. |
| >>delete from t005t04; |
| |
| --- 9 row(s) deleted. |
| >> |
| >>insert INTO t005t03 values(0,1, 0); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values(0,1, 2); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select a.pict1, b.pict1 FROM t005t03 a |
| +> FULL OUTER JOIN t005t04 b |
| +>ON a.pict1 = b.pict1 where ( b.pict1 > 1 or a.pict1 < 1 ) order by 1; |
| |
| PICT1 PICT1 |
| -------------------- -------------------- |
| |
| .00000 ? |
| ? 2.00000 |
| |
| --- 2 row(s) selected. |
| >> |
| >>?section 3-way FOJ |
| >>delete from t005t03; |
| |
| --- 1 row(s) deleted. |
| >>delete from t005t04; |
| |
| --- 1 row(s) deleted. |
| >>delete from t005t05; |
| |
| --- 0 row(s) deleted. |
| >> |
| >>insert INTO t005t03 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (2, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 (seqno) values (3); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (5, 32767,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (6,7892,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (7,2834,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (8,123,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >> |
| >>insert INTO t005t04 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (2, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (3,24923,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (5,25065,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (6,7892,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (12,9374,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >> |
| >>insert into t005t05 values (7); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- FOJ16 - columns projected from the left table only. |
| >>-- Join t005t03 FOJ t005t04 FOJ t005t05 |
| >>SELECT a.seqno, b.seqno FROM t005t03 a |
| +> FULL OUTER JOIN t005t04 b ON a.seqno = b.seqno |
| +> FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno order by 1,2; |
| |
| SEQNO SEQNO |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| 3 3 |
| 4 4 |
| 5 5 |
| 6 6 |
| 7 ? |
| 8 ? |
| ? 12 |
| ? ? |
| |
| --- 10 row(s) selected. |
| >> |
| >>-- FOJ17 - columns projected from the left table only, with a WHERE clasue |
| >>-- on the right column. |
| >>-- Join t005t03 FOJ t005t04 FOJ t005t05 |
| >>SELECT a.seqno, e.seqno FROM t005t03 a |
| +> FULL OUTER JOIN t005t04 b ON a.seqno = b.seqno |
| +> FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno where e.seqno = 7; |
| |
| SEQNO SEQNO |
| ----------- ----------- |
| |
| ? 7 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- FOJ18 - columns projected from the right table only. |
| >>-- Join t005t03 FOJ t005t04 FOJ t005t05 |
| >>SELECT e.seqno FROM t005t03 a |
| +> FULL OUTER JOIN t005t04 b ON a.seqno = b.seqno |
| +> FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno order by 1; |
| |
| SEQNO |
| ----------- |
| |
| 7 |
| ? |
| ? |
| ? |
| ? |
| ? |
| ? |
| ? |
| ? |
| ? |
| |
| --- 10 row(s) selected. |
| >> |
| >> |
| >>-- FOJ19 - columns projected from the right table only with a WHERE clause |
| >>-- on the right table column. |
| >>-- Join t005t03 FOJ t005t04 FOJ t005t05 |
| >>SELECT e.seqno FROM t005t03 a |
| +> FULL OUTER JOIN t005t04 b ON a.seqno = b.seqno |
| +> FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno where e.seqno = 7; |
| |
| SEQNO |
| ----------- |
| |
| 7 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>?section FOJ_other_join_combination |
| >>delete from t005t03; |
| |
| --- 8 row(s) deleted. |
| >>delete from t005t04; |
| |
| --- 7 row(s) deleted. |
| >>delete from t005t05; |
| |
| --- 1 row(s) deleted. |
| >> |
| >>insert INTO t005t03 values (0, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 (seqno) values (3); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t03 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >> |
| >>insert INTO t005t04 values (0, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (1, 1,1); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 (seqno) values (3); |
| |
| --- 1 row(s) inserted. |
| >>insert INTO t005t04 values (4,-32768,1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t005t05 values (1); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t05 values (2); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t05 values (3); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- FOJ20 - FOJ-INNER with ON clause |
| >>-- Join t005t03 FOJ t005t04 INNER t005t05 |
| >>SELECT seqno, smin1 |
| +> FROM |
| +> t005t03 |
| +> FULL OUTER JOIN |
| +> (select t005t04.seqno as b |
| +> from t005t04 INNER JOIN t005t05 |
| +> ON t005t04.seqno = t005t05.seqno |
| +> ) as t2 |
| +>on t005t03.seqno = t2.b |
| +>order by 1,2; |
| |
| SEQNO SMIN1 |
| ----------- ------ |
| |
| 0 1 |
| 1 1 |
| 3 ? |
| 4 -32768 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- FOJ21 - FOJ-INNER with ON clause |
| >>-- and WHERE clause on left table |
| >>-- Join t005t03 FOJ t005t04 INNER t005t05 |
| >>SELECT seqno, smin1 |
| +> FROM |
| +> t005t03 t1 |
| +> FULL OUTER JOIN |
| +> (select t005t04.seqno as b |
| +> from t005t04 INNER JOIN t005t05 |
| +> ON t005t04.seqno = t005t05.seqno |
| +> ) as t2 |
| +>on t1.seqno = t2.b |
| +>where t1.seqno < 4 order by 1; |
| |
| SEQNO SMIN1 |
| ----------- ------ |
| |
| 0 1 |
| 1 1 |
| 3 ? |
| |
| --- 3 row(s) selected. |
| >> |
| >> |
| >>-- FOJ22 - FOJ-INNER with ON clause |
| >>-- and WHERE clause on right table |
| >>-- Join t005t03 FOJ t005t04 INNER t005t05 |
| >>SELECT seqno, smin1 |
| +> FROM |
| +> t005t03 t1 |
| +> FULL OUTER JOIN |
| +> (select t005t04.seqno as b |
| +> from t005t04 INNER JOIN t005t05 |
| +> ON t005t04.seqno = t005t05.seqno |
| +> ) as t2 |
| +>on t1.seqno = t2.b |
| +>where t2.b < 4 order by 1; |
| |
| SEQNO SMIN1 |
| ----------- ------ |
| |
| 1 1 |
| 3 ? |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- FOJ23, FOJ, INNER and LOJ. |
| >>-- Soln 10-081027-6839 |
| >>-- predicate was lost because a column was incorrectly flagged as NOT outerrerfernce |
| >> |
| >>select * from t005_ex left outer join t005_fx |
| +>on (t005_ex.e3 = t005_fx.f3 and t005_ex.e2 = t005_fx.f2 and t005_ex.e1 = t005_fx.f1) |
| +>join t005_gx on (t005_ex.e1 = t005_gx.g1) |
| +>full outer join t005_hx on (t005_gx.g2 = t005_hx.h1 and t005_ex.e3 = t005_hx.h3 and t005_ex.e2 = t005_hx.h2) |
| +>order by t005_ex.e1 asc, t005_ex.e2 asc, t005_ex.e3 asc, t005_hx.h1, t005_hx.h2, t005_hx.h3; |
| |
| E1 E2 E3 F1 F2 F3 G1 G2 H1 H2 H3 |
| ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 1 1 1 1 1 1 1 2 2 1 1 |
| 1 1 2 1 1 2 1 2 2 1 2 |
| 1 1 3 1 1 3 1 2 2 1 3 |
| 1 2 1 1 2 1 1 2 2 2 1 |
| 1 2 2 1 2 2 1 2 2 2 2 |
| 1 2 4 1 2 4 1 2 ? ? ? |
| 1 3 1 1 3 1 1 2 ? ? ? |
| 1 3 2 1 3 2 1 2 ? ? ? |
| 1 3 3 1 3 3 1 2 ? ? ? |
| ? ? ? ? ? ? ? ? 1 1 1 |
| ? ? ? ? ? ? ? ? 1 1 2 |
| ? ? ? ? ? ? ? ? 1 1 3 |
| ? ? ? ? ? ? ? ? 1 1 4 |
| ? ? ? ? ? ? ? ? 1 2 1 |
| ? ? ? ? ? ? ? ? 1 2 2 |
| ? ? ? ? ? ? ? ? 1 2 3 |
| ? ? ? ? ? ? ? ? 1 3 2 |
| ? ? ? ? ? ? ? ? 1 3 3 |
| ? ? ? ? ? ? ? ? 1 3 4 |
| ? ? ? ? ? ? ? ? 2 1 4 |
| ? ? ? ? ? ? ? ? 2 2 3 |
| |
| --- 21 row(s) selected. |
| >> |
| >>-- FOJ24 |
| >>-- actual query from previous solution. Order of FOJ is different |
| >> |
| >>select * from t005_ex full outer join t005_fx |
| +>on (t005_ex.e3 = t005_fx.f3 and t005_ex.e2 = t005_fx.f2 and t005_ex.e1 = t005_fx.f1) |
| +>join t005_gx on (t005_ex.e1 = t005_gx.g1) |
| +>left outer join t005_hx on (t005_gx.g2 = t005_hx.h1 and t005_ex.e3 = t005_hx.h3 and t005_ex.e2 = t005_hx.h2) |
| +>order by t005_ex.e1 asc, t005_ex.e2 asc, t005_ex.e3 asc; |
| |
| E1 E2 E3 F1 F2 F3 G1 G2 H1 H2 H3 |
| ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- |
| |
| 1 1 1 1 1 1 1 2 2 1 1 |
| 1 1 2 1 1 2 1 2 2 1 2 |
| 1 1 3 1 1 3 1 2 2 1 3 |
| 1 2 1 1 2 1 1 2 2 2 1 |
| 1 2 2 1 2 2 1 2 2 2 2 |
| 1 2 4 1 2 4 1 2 ? ? ? |
| 1 3 1 1 3 1 1 2 ? ? ? |
| 1 3 2 1 3 2 1 2 ? ? ? |
| 1 3 3 1 3 3 1 2 ? ? ? |
| |
| --- 9 row(s) selected. |
| >> |
| >> |
| >>?section NEGATIVE_VALUES |
| >> |
| >>insert into t005t01 values (-1, -5); |
| |
| --- 1 row(s) inserted. |
| >>insert into t005t02 values (-5, 'REWS'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno order by 3; |
| |
| DNO DNAME ENO DNO |
| -------------------- -------------------- ----------- -------------------- |
| |
| -5 REWS -1 -5 |
| 33 Sales 3 33 |
| 44 Marketing 4 44 |
| 55 Production 5 55 |
| ? ? 6 66 |
| 33 Sales 30 33 |
| 77 R&D ? ? |
| |
| --- 7 row(s) selected. |
| >> |
| >>delete from t005t01 where dno = -5; |
| |
| --- 1 row(s) deleted. |
| >>delete from t005t02 where dno = -5; |
| |
| --- 1 row(s) deleted. |
| >> |
| >>?section 3-Way Join |
| >> |
| >>?section FOJ_restrictions |
| >>-- FOJ50 - Must raise an error? FOJ is only supported for Hash Joins. |
| >>-- control query default hash_joins 'off'; |
| >> |
| >>-- FOJ51 - Subquery in the join predicate of FOJ is not supported. |
| >>select * from t005t02 full outer join t005t01 on t005t01.dno = (select min(dno |
| +>) from t005t01); |
| |
| *** ERROR[4339] Subqueries are not supported in the join predicate of a Full Outer Join. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- FOJ52 - Broadcast join is not supported for FOJ. |
| >> |
| >>-- FOJ503 - MV not supported |
| >>create materialized view T_MV1 |
| +> Refresh on request |
| +> initialize on create |
| +> AS |
| +> select A.dno,B.eno |
| +> from t005t02 A full outer join t005t01 B on A.dno = B.dno; |
| |
| *** ERROR[3131] The statement just entered is currently not supported. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>create materialized view T_MV1 |
| +> Refresh on statement |
| +> initialize on create |
| +> AS |
| +> select A.dno,B.eno |
| +> from t005t02 A full outer join t005t01 B on A.dno = B.dno; |
| |
| *** ERROR[3131] The statement just entered is currently not supported. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>with w1 as (select * from witht1), |
| +>w2 as (select * from w1) |
| +>select * from w2; |
| |
| C1 C2 |
| ----------- ----------- |
| |
| 1 1 |
| 2 2 |
| 3 3 |
| 4 4 |
| 5 5 |
| |
| --- 5 row(s) selected. |
| >> |
| >>with w1 as (select c1, c2 from witht1), |
| +>w2 as (select c1,c2 from witht2) |
| +>select * from w1 , w2 where w1.c1 = w2.c1; |
| |
| C1 C2 C1 C2 |
| ----------- ----------- ----------- ----------- |
| |
| 3 3 3 3 |
| 4 4 4 4 |
| 5 5 5 5 |
| |
| --- 3 row(s) selected. |
| >> |
| >>with w1 as (select * from witht1) |
| +>select * from w1 |
| +>union all |
| +>select * from w1; |
| |
| C1 C2 |
| ----------- ----------- |
| |
| 1 1 |
| 1 1 |
| 2 2 |
| 2 2 |
| 3 3 |
| 3 3 |
| 4 4 |
| 4 4 |
| 5 5 |
| 5 5 |
| |
| --- 10 row(s) selected. |
| >> |
| >>with recursive w1 as (select c1, c2 from witht1 union all select origin.c1 , origin.c2 from w1 join t1 origin on origin.c1 = w1.c1 ); |
| |
| *** ERROR[3022] The WITH RECURSIVE operator is not yet supported. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>with w1 as (select * from witht1), w1 as (select * from witht2) select * from w1; |
| |
| *** ERROR[3288] A syntax error occurred. WITH clause redefined. WITH name W1 . |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>?section cleanup |
| >> |
| >>-- Clean up test |
| >>drop table t005t01; |
| |
| --- SQL operation complete. |
| >>drop table t005t02; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t005t03; |
| |
| --- SQL operation complete. |
| >>drop table t005t04; |
| |
| --- SQL operation complete. |
| >>drop table t005t05; |
| |
| --- SQL operation complete. |
| >> |
| >>drop table t005_ex; |
| |
| --- SQL operation complete. |
| >>drop table t005_gx; |
| |
| --- SQL operation complete. |
| >>drop table t005_hx; |
| |
| --- SQL operation complete. |
| >>drop table t005_fx; |
| |
| --- SQL operation complete. |
| >>drop table witht1; |
| |
| --- SQL operation complete. |
| >>drop table witht2; |
| |
| --- SQL operation complete. |
| >>exit; |
| |
| End of MXCI Session |
| |
