lojreorder.sql

derby database source code.good for you.

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 8000;

SELECT * FROM TNL1 A LEFT OUTER JOIN TNL1_1 B ON A.ID = B.ID LEFT OUTER JOIN  TNL1_1_1 C ON B.ID2=C.ID LEFT OUTER JOIN TNL1_2 D ON A.ID=D.ID ORDER BY 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

SELECT * FROM TNL1 A LEFT OUTER JOIN (TNL1_1 B LEFT OUTER JOIN TNL1_1_1 C ON B.ID2=C.ID) ON A.ID=B.ID LEFT OUTER JOIN TNL1_2 D ON A.ID=D.ID ORDER BY 1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);

DROP TABLE TNL1;
DROP TABLE TNL1_1;
DROP TABLE TNL1_1_1;
DROP TABLE TNL1_2;

---
-- outerjn.db


CREATE SCHEMA K55ADMIN;

CREATE TABLE K55ADMIN.PARTS
      (PART     CHAR(10),
       NUM    SMALLINT,
       SUPPLIER CHAR(20));
CREATE TABLE K55ADMIN.PARTS_T
      (PART     CHAR(10),
       NUM1    SMALLINT,
       NUM2    SMALLINT,
       SUPPLIER CHAR(20));
CREATE TABLE K55ADMIN.PARTS_NOTNULL
      (PART     CHAR(10)  NOT NULL,
       NUM    SMALLINT  NOT NULL,
       SUPPLIER CHAR(20)  NOT NULL);
CREATE TABLE K55ADMIN.PARTS_ALLNULL
      (PART     CHAR(10),
       NUM    SMALLINT,
       SUPPLIER CHAR(20));
CREATE TABLE K55ADMIN.PARTS_EMPTY
      (PART     CHAR(10),
       NUM    SMALLINT,
       SUPPLIER CHAR(20));
CREATE TABLE K55ADMIN.PARTS_EMPTY_NN
      (PART     CHAR(10)  NOT NULL,
       NUM    SMALLINT  NOT NULL,
       SUPPLIER CHAR(20)  NOT NULL);
CREATE TABLE K55ADMIN.PRODUCTS
      (NUM    SMALLINT,
       PRODUCT  CHAR(15),
       PRICE    DECIMAL(7,2));
CREATE TABLE K55ADMIN.PRODUCTS_T
      (NUM1    SMALLINT,
       NUM2    SMALLINT,
       PRODUCT  CHAR(15),
       PRICE    DECIMAL(7,2));
CREATE TABLE K55ADMIN.PRODUCTS_NOTNULL
      (NUM    SMALLINT     NOT NULL,
       PRODUCT  CHAR(15)     NOT NULL,
       PRICE    DECIMAL(7,2) NOT NULL);
CREATE TABLE K55ADMIN.PRODUCTS_ALLNULL
      (NUM    SMALLINT,
       PRODUCT  CHAR(15),
       PRICE    DECIMAL(7,2));
CREATE TABLE K55ADMIN.PRODUCTS_EMPTY
      (NUM    SMALLINT,
       PRODUCT  CHAR(15),
       PRICE    DECIMAL(7,2));
CREATE TABLE K55ADMIN.PRODUCTS_EMPTY_NN
      (NUM    SMALLINT     NOT NULL,
       PRODUCT  CHAR(15)     NOT NULL,
       PRICE    DECIMAL(7,2) NOT NULL);
CREATE TABLE K55ADMIN.S90
      (DEPT     CHAR(3)      NOT NULL,
       SALES    SMALLINT);
CREATE TABLE K55ADMIN.S91
      (DEPT     CHAR(3),
       SALES    SMALLINT);
CREATE TABLE K55ADMIN.S92
      (DEPT     CHAR(3)      NOT NULL,
       SALES    SMALLINT);
CREATE TABLE K55ADMIN.EMPLOYEES
      (EMP_ID        CHAR(6)     NOT NULL,
       EMP_NAME      VARCHAR(25),
       SALARY        INTEGER,
       COMM          SMALLINT);

CREATE UNIQUE INDEX K55ADMIN.EMPLOYIX
  ON K55ADMIN.EMPLOYEES(EMP_ID);
CREATE TABLE K55ADMIN.OLD_OFFICES
      (OLD_OFFICE    CHAR(4)     NOT NULL,
       EMP_ID        CHAR(6));
CREATE UNIQUE INDEX k55ADMIN.OLD_OFFIX
  ON K55ADMIN.OLD_OFFICES(OLD_OFFICE);
CREATE TABLE K55ADMIN.NEW_OFFICES
      (NEW_OFFICE    CHAR(4)     NOT NULL,
       EMP_ID        CHAR(6));
CREATE UNIQUE INDEX k55ADMIN.NEW_OFFIX
  ON K55ADMIN.NEW_OFFICES(NEW_OFFICE);
--************************************************************************
--*   This last group is for general usage throughout the tests          *
--************************************************************************
CREATE TABLE K55ADMIN.MANYTYPES
      (INTCOL        INTEGER,
       SMINTCOL      SMALLINT,
       DEC62COL      DECIMAL(6,2),
       DEC72COL      DECIMAL(7,2),
       FLOATCOL      FLOAT,
       CHARCOL       CHAR(10),
       LCHARCOL      CHAR(250),
       VCHARCOL      VARCHAR(100));
CREATE TABLE K55ADMIN.MANYTYPES_NOTNULL
      (INTCOL        INTEGER       NOT NULL,
       SMINTCOL      SMALLINT      NOT NULL,
       DEC62COL      DECIMAL(6,2)  NOT NULL,
       DEC72COL      DECIMAL(7,2)  NOT NULL,
       FLOATCOL      FLOAT         NOT NULL,
       CHARCOL       CHAR(15)      NOT NULL,
       LCHARCOL      CHAR(250)     NOT NULL,
       VCHARCOL      VARCHAR(100)  NOT NULL);
--* CREATE TABLE K55ADMIN.MANYTYPES_CTRL LIKE MANYTYPES_NOTNULL;
CREATE TABLE K55ADMIN.MANYTYPES_CTRL
      (INTCOL        INTEGER       NOT NULL,
       SMINTCOL      SMALLINT      NOT NULL,
       DEC62COL      DECIMAL(6,2)  NOT NULL,
       DEC72COL      DECIMAL(7,2)  NOT NULL,
       FLOATCOL      FLOAT         NOT NULL,
       CHARCOL       CHAR(15)      NOT NULL,
       LCHARCOL      CHAR(250)     NOT NULL,
       VCHARCOL      VARCHAR(100)  NOT NULL);
--************************************************************************
--* Populate the tables created above                                    *
--************************************************************************
INSERT INTO K55ADMIN.PARTS VALUES ('Wire',     10,'ACWF');
INSERT INTO K55ADMIN.PARTS VALUES ('Oil',     160,'Western-Chem');
INSERT INTO K55ADMIN.PARTS VALUES ('Magnets',  10,'Bateman');
INSERT INTO K55ADMIN.PARTS VALUES ('Plastic',  30,'Plastik-Corp');
INSERT INTO K55ADMIN.PARTS VALUES ('Blades',  205,'Ace-Steel');
INSERT INTO K55ADMIN.PARTS VALUES ('Paper',    20,'Ace-Steel');
INSERT INTO K55ADMIN.PARTS VALUES ('Steel',    30,'ACWF');
--*
INSERT INTO K55ADMIN.PARTS_ALLNULL VALUES (NULL,NULL,NULL);
INSERT INTO K55ADMIN.PARTS_ALLNULL VALUES (NULL,NULL,NULL);
INSERT INTO K55ADMIN.PARTS_ALLNULL VALUES (NULL,NULL,NULL);
--*
INSERT INTO K55ADMIN.PARTS_NOTNULL
       SELECT * FROM K55ADMIN.PARTS;
INSERT INTO K55ADMIN.PARTS_T
       SELECT PART, NUM, 10+NUM,SUPPLIER FROM K55ADMIN.PARTS WHERE K55ADMIN.PARTS.NUM>10;
INSERT INTO K55ADMIN.PARTS VALUES (NULL,    30,NULL);
INSERT INTO K55ADMIN.PARTS_T VALUES ('Unknown', NULL, NULL, NULL);
--*
INSERT INTO K55ADMIN.PRODUCTS VALUES (505, 'Screwdriver',  3.70);
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 30, 'Relay',        7.55);
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 50, 'Hammer',       5.75);
INSERT INTO K55ADMIN.PRODUCTS VALUES (205, 'Saw',         18.90);
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 10, 'Generator',   45.75);
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 20, 'Sander',      35.75);
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 30, 'Ruler',        8.75);
--*
INSERT INTO K55ADMIN.PRODUCTS_NOTNULL
       SELECT * FROM K55ADMIN.PRODUCTS;
INSERT INTO K55ADMIN.PRODUCTS_T
       SELECT NUM, 10+NUM, PRODUCT, PRICE  FROM K55ADMIN.PRODUCTS WHERE PRICE>7;
INSERT INTO K55ADMIN.PRODUCTS VALUES ( 20, NULL, NULL);
INSERT INTO K55ADMIN.PRODUCTS_T VALUES ( NULL, NULL, 'Unknown', NULL);
--*
INSERT INTO K55ADMIN.PRODUCTS_ALLNULL VALUES (NULL, NULL, NULL);
INSERT INTO K55ADMIN.PRODUCTS_ALLNULL VALUES (NULL, NULL, NULL);
INSERT INTO K55ADMIN.PRODUCTS_ALLNULL VALUES (NULL, NULL, NULL);
--*
INSERT INTO K55ADMIN.S90 VALUES ('M62',10);
INSERT INTO K55ADMIN.S90 VALUES ('M09',99);
INSERT INTO K55ADMIN.S90 VALUES ('J64',64);
INSERT INTO K55ADMIN.S91 VALUES ('M62',100);
INSERT INTO K55ADMIN.S91 VALUES ('M09',10);
INSERT INTO K55ADMIN.S91 VALUES ('M03',500);
INSERT INTO K55ADMIN.S92 VALUES ('M62',50);
INSERT INTO K55ADMIN.S92 VALUES ('M03',10);
INSERT INTO K55ADMIN.S92 VALUES ('J64',50);
--*
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('711276','J. Thomas',75000,1500);
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('480923','C. Manthey',33500, 500);
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('368521','B. Ward',46700,0);
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('966641','K. Woods',41300,350);
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('537260',NULL,0,0);
INSERT INTO K55ADMIN.EMPLOYEES VALUES ('216861','N. Baxter',52000,550);
--*
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X124','480923');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X125','711276');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X126','988870');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X127','368521');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X128','537260');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X129','622273');
INSERT INTO K55ADMIN.OLD_OFFICES VALUES ('X130',NULL    );
--*
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y124','537260');
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y125','368521');
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y126','711276');
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y127',NULL    );
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y128','480923');
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y129','216861');
INSERT INTO K55ADMIN.NEW_OFFICES VALUES ('Y130','333666');
--*
INSERT INTO K55ADMIN.MANYTYPES VALUES (1,1,1.0,1.0,1E0,'One','One', 'One');
INSERT INTO K55ADMIN.MANYTYPES VALUES (2,2,2.0,2.0,2E0,'Two','Two', 'Two');
INSERT INTO K55ADMIN.MANYTYPES VALUES (3,3,3.0,3.0,3E0,'Three','Three', 'Three');
INSERT INTO K55ADMIN.MANYTYPES VALUES (4,4,4.0,4.0,4E0,'Four','Four', 'Four');
INSERT INTO K55ADMIN.MANYTYPES VALUES (5,5,5.0,5.0,5E0,'Five','Five', 'Five');
INSERT INTO K55ADMIN.MANYTYPES VALUES (6,6,6.0,6.0,6E0,'Six','Six', 'Six');
INSERT INTO K55ADMIN.MANYTYPES VALUES (7,7,7.0,7.0,7E0,'Seven','Seven', 'Seven');
INSERT INTO K55ADMIN.MANYTYPES VALUES (8,8,8.0,8.0,8E0,'Eight','Eight', 'Eight');
INSERT INTO K55ADMIN.MANYTYPES VALUES (9,9,9.0,9.0,9E0,'Nine','Nine', 'Nine');
INSERT INTO K55ADMIN.MANYTYPES VALUES (10,10,10.0,10.0,1E1,'Ten','Ten', 'Ten');
INSERT INTO K55ADMIN.MANYTYPES VALUES (11,11,11.0,11.0,1.1E1,'Eleven', 'Eleven','Eleven');
INSERT INTO K55ADMIN.MANYTYPES VALUES (12,12,12.0,12.0,1.2E1,'Twelve', 'Twelve','Twelve');
INSERT INTO K55ADMIN.MANYTYPES VALUES (13,13,13.0,13.0,1.3E1,'Thirteen', 'Thirteen','Thirteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (14,14,14.0,14.0,1.4E1,'Fourteen', 'Fourteen','Fourteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (15,15,15.0,15.0,1.5E1,'Fifteen', 'Fifteen','Fifteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (16,16,16.0,16.0,1.6E1,'Sixteen', 'Sixteen','Sixteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (17,17,17.0,17.0,1.7E1,'Seventeen', 'Seventeen','Seventeen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (18,18,18.0,18.0,1.8E1,'Eighteen', 'Eighteen','Eighteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (19,19,19.0,19.0,1.9E1,'Nineteen', 'Nineteen','Nineteen');
INSERT INTO K55ADMIN.MANYTYPES VALUES (20,20,20.0,20.0,2E1,'Twenty', 'Twenty','Twenty');
--*
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (11,11,11.0,11.0,1.1E1,'Eleven','Eleven', 'Eleven');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (12,12,12.0,12.0,1.2E1,'Twelve','Twelve', 'Twelve');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (13,13,13.0,13.0,1.3E1,'Thirteen', 'Thirteen','Thirteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (14,14,14.0,14.0,1.4E1,'Fourteen', 'Fourteen','Fourteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (15,15,15.0,15.0,1.5E1,'Fifteen', 'Fifteen','Fifteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (16,16,16.0,16.0,1.6E1,'Sixteen', 'Sixteen','Sixteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (17,17,17.0,17.0,1.7E1,'Seventeen', 'Seventeen','Seventeen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (18,18,18.0,18.0,1.8E1,'Eighteen', 'Eighteen','Eighteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (19,19,19.0,19.0,1.9E1,'Nineteen', 'Nineteen','Nineteen');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (20,20,20.0,20.0,2E1,'Twenty','Twenty', 'Twenty');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (21,21,21.0,21.0,2.1E1,'Twenty One','Twenty One', 'Twenty One');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (22,22,22.0,22.0,2.2E1,'Twenty Two','Twenty Two', 'Twenty Two');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (23,23,23.0,23.0,2.3E1,'Twenty Three','Twenty Three', 'Twenty Three');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (24,24,24.0,24.0,2.4E1,'Twenty Four','Twenty Four', 'Twenty Four');
INSERT INTO K55ADMIN.MANYTYPES_NOTNULL VALUES
   (25,25,25.0,25.0,2.5E1,'Twenty Five','Twenty Five', 'Twenty Five');
--*
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (2,2,2.0,2.0,2E0,'Two','Two', 'Two');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (4,4,4.0,4.0,4E0,'Four','Four', 'Four');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (6,6,6.0,6.0,6E0,'Six','Six', 'Six');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (8,8,8.0,8.0,8E0,'Eight','Eight', 'Eight');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (10,10,10.0,10.0,1E1,'Ten','Ten', 'Ten');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (12,12,12.0,12.0,1.2E1,'Twelve','Twelve', 'Twelve');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (14,14,14.0,14.0,1.4E1,'Fourteen', 'Fourteen','Fourteen');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES
   (16,16,16.0,16.0,1.6E1,'Sixteen', 'Sixteen','Sixteen');
INSERT INTO K55ADMIN.MANYTYPES_CTRL VALUES