lojreorder.sql

derby database source code.good for you.

   (18,18,18.0,18.0,1.8E1,'Eighteen', 'Eighteen','Eighteen');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (20,20,20.0,20.0,2E1,'Twenty','Twenty', 'Twenty');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (22,22,22.0,22.0,2.2E1,'Twenty Two','Twenty Two', 'Twenty Two');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (24,24,24.0,24.0,2.4E1,'Twenty Four','Twenty Four', 'Twenty Four');


-- coj201.clp

-- ---------------------------------------------------------------------;
-- test unit 1. OUTER JOIN w/ correlated subquery;
-- ---------------------------------------------------------------------;

-- 101 - 'between' in LEFT JOIN ON condition;
--       '>=all' subquery correlated to tuple-preserving operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price+20 >= all
       (select a.price
        from k55admin.products a
        where a.num>=p.num)
order by 1,2,3,4;

-- 102 - 'between' in RIGHT JOIN ON condition;
--       '>=all' subquery correlated to tuple-preserving operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price+20 >= all
       (select a.price
        from k55admin.products a
        where a.num>=p.num)
order by 1,2,3,4;

-- 103 - '='/like/in in LEFT JOIN ON condition;
--       '>=all' subquery correlated to null-producing operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price+20 >= all
       (select a.price
        from k55admin.products a
        where a.num>=pt.num)
order by 1,2,3,4;

-- 104 - '='/like/in in RIGHT JOIN ON condition;
--       '>=all' subquery correlated to null-producing operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price+20 >= all
       (select a.price
        from k55admin.products a
        where a.num>=pt.num)
order by 1,2,3,4;

-- 105 - 'between' in LEFT JOIN ON condition;
--       IN subquery correlated to tuple-preserving operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price IN
       (select a.price
        from k55admin.products a
        where a.num>=p.num)
order by 1,2,3,4;

-- 106 - 'between' in RIGHT JOIN ON condition;
--       IN subquery correlated to tuple-preserving operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price IN
       (select a.price
        from k55admin.products a
        where a.num>=p.num)
order by 1,2,3,4;

-- 107 - '='/like/in in LEFT JOIN ON condition;
--       IN subquery correlated to null-producing operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price IN
       (select a.price
        from k55admin.products a
        where a.num>=pt.num)
order by 1,2,3,4;

-- 108 - '='/like/in in RIGHT JOIN ON condition;
--       IN subquery correlated to null-producing operand in the WHERE clause;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price IN
       (select a.price
        from k55admin.products a
        where a.num>=pt.num)
order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 2. nested subquery: OUTER JOIN w/ correlated OUTER JOIN subquery;
-- ---------------------------------------------------------------------;

-- 201 - 'between' in LEFT JOIN ON condition;
--       '>=all' subquery correlated to tuple-preserving operand in the WHERE clause;
--       subquery: local pred on tuple-preserving operand, '=' join predicate, and, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price+20 >= all
       (select a.price
        from k55admin.parts b LEFT JOIN k55admin.products a
        on   b.num = a.num and b.part <> 'Wire'
        where a.num>=p.num)
order by 1,2,3,4;

-- 202 - 'between' in RIGHT JOIN ON condition;
--       '>=all' subquery correlated to tuple-preserving operand in the WHERE clause;
--       subquery: local pred on both operands, '=' join predicate, and, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price+20 >= all
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on   a.num = b.num and (a.price>8 or b.part = 'Steel') and a.num > 20
        where a.num>=p.num)
order by 1,2,3,4;

-- 203 - =/like/in in LEFT JOIN ON condition;
--       '>=all' subquery correlated to null-producing operand in the WHERE clause;
--       subquery: local '=' pred on null-producing operand OR '=' join predicate, right join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price+20 >= all
       (select a.price
        from k55admin.parts b right join k55admin.products a
        on   b.num = 10 OR a.num = b.num
        where a.num>=pt.num)
order by 1,2,3,4;

-- 204 - '='/like/in in RIGHT JOIN ON condition;
--       '>=all' subquery correlated to null-producing operand in the WHERE clause;
--       subquery: local pred on both operands, '=' join predicate, and/or, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price+20 >= all
       (select a.price
        from   k55admin.parts b left join k55admin.products a
        on     a.num = b.num and (a.price>8 or b.part = 'Steel' or b.num > 20)
        where a.num>=pt.num)
order by 1,2,3,4;

-- 205 - 'between' in LEFT JOIN ON condition;
--       IN subquery correlated to tuple-preserving operand in the WHERE clause;
--       subquery: local '=' pred on tuple-preserving operand OR '=' join predicate, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price IN
       (select a.price
        from   k55admin.parts b left join k55admin.products a
        on     b.num = 10 OR a.num = b.num
        where a.num>=p.num)
order by 1,2,3,4;

-- 206 - 'between' in RIGHT JOIN ON condition;
--       IN subquery correlated to tuple-preserving operand in the WHERE clause;
--       subquery: local '=' pred on tuple-preserving operand AND '<>' join predicate, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on   (p.num between pt.num and pt.num + 5)
where pt.price IN
       (select a.price
        from   k55admin.parts b left join k55admin.products a
        on     b.num = 1000 and a.num <> b.num
        where a.num>=p.num)
order by 1,2,3,4;

-- 207 - '='/like/in in LEFT JOIN ON condition;
--       IN subquery correlated to null-producing operand in the WHERE clause;
--       subquery: '=' join predicate and '2>1', left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price IN
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on   a.num = b.num and 2>1
        where a.num>=pt.num)
order by 1,2,3,4;

-- 208 - '='/like/in in RIGHT JOIN ON condition;
--       IN subquery correlated to null-producing operand in the WHERE clause;
--       subquery: '1=0' in ON condition, left join;
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
where pt.price IN
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0
        where a.num>=pt.num)
order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 3. subquery in OUTER JOIN ON clause;
-- Not allowed in DB2 compatibility mode
-- ---------------------------------------------------------------------;

-- 301 - '='/like/in in RIGHT JOIN ON condition with correlated IN subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
and pt.price IN
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0
        where a.num>=pt.num)
order by 1,2,3,4;

-- 302 - '='/like/in in LEFT JOIN ON condition with uncorrelated IN subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on (pt.product in ('Bolt','Nuts') or
    pt.product like 'Power%' or
    p.num = pt.num)
and pt.price IN
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0)
order by 1,2,3,4;

-- 303 - '=' and inlist in RIGHT JOIN ON condition with correlated exists subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p right join k55admin.products pt
on pt.product in ('Bolt','Nuts') and p.num = pt.num
and exists
       (select a.price
        from k55admin.parts b left join k55admin.products a
        on 1=0
        where a.num>=pt.num)
order by 1,2,3,4;

-- 304 - '=' and inlist in LEFT JOIN ON condition with uncorrelated exists subquery; Error.
select part, p.num, product, pt.NUM, price
from k55admin.parts p left join k55admin.products pt
on pt.product in ('Bolt','Nuts') and p.num = pt.num
and exists