lojreorder.sql

derby database source code.good for you.

-- 404 - local '=' pred on null-producing operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = 10
    order by 1,2,3,4;

-- 405 - local IN pred on null-producing operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM IN (10, 20)
    order by 1,2,3,4;

-- 406 - local between pred on null-producing operand;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM between 10 and 50
    order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 5. plain joins, local predicate and join predicate;
-- ---------------------------------------------------------------------;

-- 501 - local pred on tuple-preserving operand, '=' join predicate, and;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM and
       K55ADMIN.PARTS.PART <> 'Wire'
    order by 1,2,3,4;

-- 502 - local pred on both operands, '=' join predicate, and;
SELECT K55ADMIN.PARTS.*, K55ADMIN.PRODUCTS.*
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM and
       (K55ADMIN.PRODUCTS.PRICE>8 or K55ADMIN.PARTS.PART = 'Steel')
        and K55ADMIN.PRODUCTS.NUM > 20
    order by 1,2,3,4,5,6;

-- 503 - local pred on both operands, '=' join predicate, and/or;
SELECT K55ADMIN.PARTS.*, K55ADMIN.PRODUCTS.*
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM and
       (K55ADMIN.PRODUCTS.PRICE>8 or K55ADMIN.PARTS.PART = 'Steel'
        or K55ADMIN.PRODUCTS.NUM > 20)
    order by 1,2,3,4,5,6;

-- 504 - local '=' pred on null-producing operand OR '=' join predicate;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS RIGHT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = 10 OR
       K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 505 - local '=' pred on tuple-preserving operand OR '=' join predicate;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = 10 OR
       K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- 506 - local '=' pred on tuple-preserving operand AND '<>' join predicate;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = 1000 AND
       K55ADMIN.PARTS.NUM <> K55ADMIN.PRODUCTS.NUM
    order by 1,2,3,4;

-- ---------------------------------------------------------------------;
-- test unit 6. plain joins, '1=0' or '2>1' predicate, w/ or w/o join predicate;
-- ---------------------------------------------------------------------;

-- 601 - '=' join predicate and '2>1';
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM and
       2>1
    order by 1,2,3,4;

-- 602 - '=' join predicate and '1=0';
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM and
       1=0
    order by 1,2,3,4;

-- 603 - '=' join predicate or '2>1';
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM or
       2>1
    order by 1,2,3,4;

-- 604 - '=' join predicate or '1=0';
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON K55ADMIN.PARTS.NUM = K55ADMIN.PRODUCTS.NUM or
       1=0
    order by 1,2,3,4;

-- 605 - '2>1' in ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON 2>1
    order by 1,2,3,4;

-- 606 - '1=0' in ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON 1=0
    order by 1,2,3,4;

-- 607 - '1=1' in ON condition;
SELECT PART, K55ADMIN.PARTS.NUM, PRODUCT, K55ADMIN.PRODUCTS.NUM
  FROM K55ADMIN.PARTS LEFT JOIN K55ADMIN.PRODUCTS
    ON 1=1
    order by 1,2,3,4;



-- coj204.clp


---------------------------------
-- Creating a table;
---------------------------------
create table k55admin.stru (
             cd_plant           varchar(5) not null,
             in_reseq           varchar(1) not null,
             no_level           integer    not null,
             no_part_base_nxt_a varchar(9) not null,
             no_part_pref_nxt_a varchar(7) not null,
             no_part_suff_nxt_a varchar(8) not null,
             no_part_cntl_nxt_a varchar(3) not null,
             no_seq             integer    not null,
             no_part_base       varchar(9) not null,
             no_part_prefix     varchar(7) not null,
             no_part_suffix     varchar(8) not null,
             no_part_control    varchar(3) not null);

---------------------------------
-- Start of loading data into table;
---------------------------------

insert into k55admin.stru values('EE01A','N',0,'','','','',0,'BLPO','R','','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',30,'M10A63','R','AH','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',40,'M10A55','R','AH','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',51,'M10A61','R','AH','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',55,'STH1008','R','','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',61,'M99G124','R','AH','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',71,'STH1050','R','','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',80,'PMIC3','R','','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',90,'M99G24','R','','');
insert into k55admin.stru values('EE01A','N',1,'BLPO','R','','',100,'M10A57','R','BH','');
insert into k55admin.stru values('EE01A','N',4,'9A937','D9AE','AA','',1,'9A301','RC5OF','A','');
insert into k55admin.stru values('EE01A','N',2,'9509','E2DE','UA','',130,'9J547','D9AE','CA','');
insert into k55admin.stru values('EE01A','N',2,'9509','E2DE','UA','',140,'9J563','E0AE','BA','');
insert into k55admin.stru values('EE01A','N',2,'9509','E2DE','UA','',141,'385338','','S2','');
insert into k55admin.stru values('EE01A','N',2,'9509','E2DE','UA','',150,'387155','R','S','');
insert into k55admin.stru values('EE01A','N',2,'9509','E2DE','UA','',160,'9E957','E2AE','P82','');
insert into k55admin.stru values('EE01A','N',3,'9E957','E2AE','P82','',1,'9E957','E2AE','BB','');
insert into k55admin.stru values('EE01A','N',3,'9E957','E2AE','P82','',2,'9S555','E37E','CA','');
insert into k55admin.stru values('EE01A','N',4,'9S555','E37E','CA','',1,'9S555','RE2AE','BA','');
insert into k55admin.stru values('EE01A','N',3,'9E957','E2AE','P82','',3,'9S554','D2AF','DA','');
insert into k55admin.stru values('EE01A','N',5,'10304','E9DF','AA','',20,'N806017','','S40G','');
insert into k55admin.stru values('EE01A','N',5,'10304','E9DF','AA','',35,'10A318','PE2AF','BA','');
insert into k55admin.stru values('EE01A','N',6,'10A318','PE2AF','BA','',10,'10A318','E2AF','BA','');
insert into k55admin.stru values('EE01A','N',7,'10A318','E2AF','BA','',10,'10A318','RE2HF','AA','');
insert into k55admin.stru values('EE01A','N',7,'10A318','E2AF','BA','',20,'48ZP42842','','','');
insert into k55admin.stru values('EE01A','N',5,'10304','E9DF','AA','',40,'10A319','PD5VF','AA','');
insert into k55admin.stru values('EE01A','N',6,'10A319','PD5VF','AA','',10,'10A319','D5VF','AA','');
insert into k55admin.stru values('EE01A','N',7,'10A319','D5VF','AA','',10,'10A319','RD5VF','AA','');
insert into k55admin.stru values('EE01A','N',5,'10304','E9DF','AA','',50,'10B301','D2OF','AA','');
insert into k55admin.stru values('EE01A','N',5,'10304','E9DF','AA','',65,'384758','','S','');
insert into k55admin.stru values('EE01A','N',4,'9E950','E6AE','AA','',1,'9E950','RE3EE','BR','');
insert into k55admin.stru values('EE01A','N',2,'9E926','E8DE','FAIP','',61,'804064','N','S2','');
insert into k55admin.stru values('EE01A','N',2,'9E926','E8DE','FAIP','',70,'803853','N','S100','');
insert into k55admin.stru values('EE01A','N',2,'9E926','E8DE','FAIP','',80,'9A776','E6AE','DF','');
insert into k55admin.stru values('EE01A','N',3,'9A776','E6AE','DF','',1,'9A794','E6AE','BD1','');
insert into k55admin.stru values('EE01A','N',4,'9A794','E6AE','BD1','',1,'9A794','PE6AE','BD','');
insert into k55admin.stru values('EE01A','N',5,'9A794','PE6AE','BD','',1,'9A794','E6AE','BD','');
insert into k55admin.stru values('EE01A','N',6,'9A794','E6AE','BD','',1,'9A794','RE69E','BB','');
insert into k55admin.stru values('EE01A','N',4,'9A794','E6AE','BD1','',2,'9E551','PE0ZE','AA','');
insert into k55admin.stru values('EE01A','N',5,'9E551','PE0ZE','AA','',1,'9E551','HE0ZE','AA','');
insert into k55admin.stru values('EE01B','N',7,'9533','HD7ZE','AA','',1,'9533','D7ZE','AA','');
insert into k55admin.stru values('EE01B','N',8,'9533','D7ZE','AA','',1,'9533','RD7ZE','AA','');
insert into k55admin.stru values('EE01B','N',4,'9E509','D9ZE','BE1','',40,'9A574','D1AF','FA','');
insert into k55admin.stru values('EE01B','N',5,'9A574','D1AF','FA','',1,'9A521','RC1AE','B','');
insert into k55admin.stru values('EE01B','N',4,'9E509','D9ZE','BE1','',50,'390468','','S','');
insert into k55admin.stru values('EE01B','N',4,'9E509','D9ZE','BE1','',60,'9A521','D0AF','AT','');
insert into k55admin.stru values('EE01B','N',5,'9A521','D0AF','AT','',1,'9A521','RC1AE','B','');
insert into k55admin.stru values('EE01B','N',4,'9E509','D9ZE','BE1','',70,'390468','','S','');
insert into k55admin.stru values('EE01B','N',4,'9E509','D9ZE','BE1','',80,'9A521','C9AF','AA','');
insert into k55admin.stru values('EE01B','N',5,'9A521','C9AF','AA','',1,'9A521','RD0AF','CV','');
insert into k55admin.stru values('EE01B','N',4,'9C511','E3ZE','AA','',1,'9C511','RE3ZE','AA','');
insert into k55admin.stru values('EE01B','N',3,'9B559','E3ZE','AA','',60,'9834','E3TE','AA','');
insert into k55admin.stru values('EE01B','N',3,'9B559','E3ZE','AA','',70,'9B551','E3ZE','AA','');
insert into k55admin.stru values('EE01B','N',4,'9B551','E3ZE','AA','',1,'9B551','RE3ZE','AA','');
insert into k55admin.stru values('EE01B','N',3,'9B559','E3ZE','AA','',80,'9934','E3ZE','AA','');
insert into k55admin.stru values('EE01B','N',2,'9509','E4TE','AMA','',370,'9D587','E3TE','ABP1','');
insert into k55admin.stru values('EE01B','N',3,'9D587','E3TE','ABP1','',10,'9D587','E3TE','AB','');
insert into k55admin.stru values('EE01B','N',4,'9D587','E3TE','AB','',1,'9D587','','5','');
insert into k55admin.stru values('EE01B','N',5,'9D587','','5','',1,'SAE303','R','M','');
insert into k55admin.stru values('EE01B','N',3,'9D587','E3TE','ABP1','',20,'9529','E3AE','AA','');
insert into k55admin.stru values('EE01B','N',5,'17B517','E59F','AA','',20,'17C526','E0TF','AA','');
insert into k55admin.stru values('EE01B','N',5,'17B517','E59F','AA','',30,'17A530','E0TF','AA','');
insert into k55admin.stru values('EE01B','N',5,'17B517','E59F','AA','',40,'17B559','E59F','AA','');
insert into k55admin.stru values('EE01B','N',4,'17B443','E59F','AA','',35,'17B583','E2TF','AA','');
insert into k55admin.stru values('EE01B','N',4,'17B443','E59F','AA','',40,'63757','','S7','');
insert into k55admin.stru values('EE01B','N',4,'17B443','E59F','AA','',50,'17A482','E0TF','AA','');
insert into k55admin.stru values('EE01B','N',2,'17508','E69F','AA','',30,'17A425','E59F','AA','');
insert into k55admin.stru values('EE01B','N',2,'17508','E69F','AA','',50,'17B584','E59F','AB','');
insert into k55admin.stru values('EE01B','N',3,'17B584','E59F','AB','',10,'17B558','E5TF','AA','');
insert into k55admin.stru values('EE01B','N',3,'17B584','E59F','AB','',20,'17C449','E0TF','AA','');
insert into k55admin.stru values('EE01D','N',3,'9512','D5TE','EA1','',60,'9996','D5AE','CB','');
insert into k55admin.stru values('EE01D','N',3,'9512','D5TE','EA1','',70,'6B608','D3AF','AA','');
insert into k55admin.stru values('EE01D','N',2,'9509','D4PE','BGA','',20,'9A521','C9AF','AK','');
insert into k55admin.stru values('EE01D','N',3,'9A521','C9AF','AK','',1,'9A521','RD0AF','CV','');
insert into k55admin.stru values('EE01D','N',2,'9509','D4PE','BGA','',30,'9A521','E3AE','BA','');
insert into k55admin.stru values('EE01D','N',2,'9509','D4PE','BGA','',40,'9576','E3ZE','AA','');
insert into k55admin.stru values('EE01D','N',2,'9509','D4PE','BGA','',50,'9A565','D5DE','BA','');
insert into k55admin.stru values('EE01D','N',3,'9A565','D5DE','BA','',10,'9E501','D5AE','AA','');
insert into k55admin.stru values('EE01D','N',4,'9E501','D5AE','AA','',1,'9E501','5','2V5','');
insert into k55admin.stru values('EE01D','N',5,'9E501','5','2V5','',1,'SAE903','R','','');
insert into k55admin.stru values('EE01D','N',3,'9512','E2ZE','MA4','',10,'9513','E2ZE','MAPE','');
insert into k55admin.stru values('EE01D','N',4,'9513','E2ZE','MAPE','',1,'9513','','2V136','');
insert into k55admin.stru values('EE01D','N',5,'9513','','2V136','',1,'SAE303','R','M','');
insert into k55admin.stru values('EE01D','N',3,'9512','E2ZE','MA4','',11,'M4G238','RESH','A','');
insert into k55admin.stru values('EE01D','N',3,'9512','E2ZE','MA4','',12,'35','R14','760','');
insert into k55admin.stru values('EE01D','N',3,'9512','E2ZE','MA4','',20,'9A521','D0AF','FD','');
insert into k55admin.stru values('EE01D','N',4,'9A521','D0AF','FD','',1,'9A521','RD0AF','CV','');
insert into k55admin.stru values('EE01D','N',3,'9512','E2ZE','MA4','',30,'9581','E2ZE','CA','');
insert into k55admin.stru values('EE01D','N',4,'9581','E2ZE','CA','',1,'9582','PD7AE','CA','');
insert into k55admin.stru values('EE01D','N',5,'9582','PD7AE','CA','',1,'9582','D7AE','CA','');
insert into k55admin.stru values('EE01D','N',5,'10379','E7AF','AA1','',10,'10379','E7AF','AA','');
insert into k55admin.stru values('EE01D','N',3,'10335','E7HF','AA','',55,'10328','E5AF','AA','');
insert into k55admin.stru values('EE01D','N',3,'10335','E7HF','AA','',60,'10B302','E7HF','AA','');
insert into k55admin.stru values('EE01D','N',4,'10B302','E7HF','AA','',20,'10B302','','2','');
insert into k55admin.stru values('EE01D','N',5,'10B302','','2','',10,'SAE303','R','M','');
insert into k55admin.stru values('EE01D','N',2,'10C335','E2HF','AA','',30,'351124','','S36','');
insert into k55admin.stru values('EE01D','N',2,'10C335','E2HF','AA','',35,'391042','','S2','');
insert into k55admin.stru values('EE01D','N',2,'10C335','E2HF','AA','',40,'389767','','S36','');
insert into k55admin.stru values('EE01D','N',2,'10C335','E2HF','AA','',50,'375026','','S36','');
insert into k55admin.stru values('EE01D','N',2,'10C335','E2HF','AA','',60,'10343','D0AF','A','');


-- ---------------------------------------------------------------------;
-- test unit 1. OJ in a scalar subquery in WHERE clause;
--              inequality predicate in ON clause and a WHERE clause;
-- ---------------------------------------------------------------------;

-- 101 - LEFT JOIN in a scalar subquery in WHERE clause;
select cd_plant, no_part_base_nxt_a
from k55admin.stru
where cd_plant = ( select  distinct b.cd_plant
                   from k55admin.stru a left join k55admin.stru b
                   on a.no_level = b.no_level and b.no_level>7
                   where a.cd_plant = 'EE01B' and b.cd_plant is not null
                 )
      and no_level < 3
;

-- 102 - RIGHT JOIN in a scalar subquery in WHERE clause;
select cd_plant, no_part_base_nxt_a
from k55admin.stru
where cd_plant = ( select  distinct b.cd_plant
                   from k55admin.stru a right join k55admin.stru b
                   on a.no_level = b.no_level and b.no_level>7
                   where a.cd_plant = 'EE01B' and b.cd_plant is not null
                 )
      and no_level < 3
;

-- ----------------------------------------