Quellcode-Bibliothek dml014.mco

-- SQL Test Suite, V6.0, SQL Module dml014.mco
-- 59-byte ID

MODULE DML014
LANGUAGE COBOL
AUTHORIZATION HU



PROCEDURE AUTHCK SQLCODE
  :UIDX CHAR(18);
      SELECT USER INTO :UIDX FROM HU.ECCO;

PROCEDURE SUB1 SQLCODE
    :PNO1 CHAR(3);
      SELECT PNUM
                  INTO    :PNO1
                  FROM   PROJ
                  WHERE  BUDGET BETWEEN 40000 AND 60000;

PROCEDURE SUB2 SQLCODE
    :PNO2 CHAR(3);
      SELECT PNUM
                  INTO    :PNO2
                  FROM   PROJ
                  WHERE BUDGET >= 40000 AND BUDGET <= 60000;

PROCEDURE SUB3 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0045','pass','MCO');

PROCEDURE SUB4 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0045','fail','MCO');

PROCEDURE SUB5 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB6 SQLCODE
    :CITY1 CHAR(15);
      SELECT CITY
                  INTO    :CITY1
                  FROM   STAFF
                  WHERE  GRADE NOT BETWEEN 12 AND 13;

PROCEDURE SUB7 SQLCODE
    :CITY2 CHAR(15);
      SELECT CITY
                  INTO    :CITY2
                  FROM   STAFF
                  WHERE  NOT( GRADE BETWEEN 12 AND 13 );

PROCEDURE SUB8 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0046','pass','MCO');

PROCEDURE SUB9 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0046','fail','MCO');

PROCEDURE SUB10 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB11 SQLCODE
    :EMPNA1 CHAR(20);
      SELECT STAFF.EMPNAME
                  INTO    :EMPNA1
                  FROM   STAFF
                  WHERE  STAFF.EMPNUM IN
                  (  SELECT WORKS.EMPNUM
                  FROM   WORKS
                  WHERE  WORKS.PNUM IN
                  (  SELECT PROJ.PNUM
                  FROM   PROJ
                  WHERE  PROJ.CITY='Tampa' ));

PROCEDURE SUB12 SQLCODE
    :EMPNA2 CHAR(20);
      SELECT STAFF.EMPNAME
                  INTO    :EMPNA2
                  FROM   STAFF
                  WHERE  STAFF.EMPNUM = ANY
                  (  SELECT WORKS.EMPNUM
                  FROM   WORKS
                  WHERE  WORKS.PNUM IN
                  (  SELECT PROJ.PNUM
                  FROM   PROJ
                  WHERE  PROJ.CITY='Tampa' ));

PROCEDURE SUB13 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0047','pass','MCO');

PROCEDURE SUB14 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0047','fail','MCO');

PROCEDURE SUB15 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB16 SQLCODE
    :HOURS1 NUMERIC (9);
      SELECT WORKS.HOURS
                  INTO    :HOURS1
                  FROM   WORKS
                  WHERE  WORKS.PNUM NOT IN 
                  (  SELECT PROJ.PNUM
                  FROM   PROJ
                  WHERE  PROJ.BUDGET  BETWEEN 5000 AND 40000 );

PROCEDURE SUB17 SQLCODE
    :HOURS2 NUMERIC (9);
      SELECT WORKS.HOURS
                  INTO    :HOURS2
                  FROM   WORKS
                  WHERE  NOT ( WORKS.PNUM IN 
                  (  SELECT PROJ.PNUM
                  FROM   PROJ
                  WHERE  PROJ.BUDGET  BETWEEN 5000 AND 40000 ));

PROCEDURE SUB18 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0048','pass','MCO');

PROCEDURE SUB19 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0048','fail','MCO');

PROCEDURE SUB20 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB21 SQLCODE
    :HOURS1 NUMERIC (9);
      SELECT HOURS
                  INTO    :HOURS1
                  FROM   WORKS
                  WHERE  PNUM NOT IN 
                  ( SELECT PNUM
                  FROM   WORKS
                  WHERE PNUM IN ('P1','P2','P4','P5','P6'));

PROCEDURE SUB22 SQLCODE
    :HOURS2 NUMERIC (9);
      SELECT HOURS
                  INTO    :HOURS2
                  FROM   WORKS
                  WHERE  NOT ( PNUM IN 
                  ( SELECT PNUM
                  FROM   WORKS
                  WHERE PNUM IN ('P1','P2','P4','P5','P6')));

PROCEDURE SUB23 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0049','pass','MCO');

PROCEDURE SUB24 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0049','fail','MCO');

PROCEDURE SUB25 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB26 SQLCODE
    :EMPNA1 CHAR(20)
    :PNO1   CHAR(3);
      SELECT EMPNAME
                  INTO    :EMPNA1
                  FROM   STAFF
                  WHERE  EMPNAME LIKE :PNO1;

PROCEDURE SUB27 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0050','pass','MCO');

PROCEDURE SUB28 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0050','fail','MCO');

PROCEDURE SUB29 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB30 SQLCODE
    :CITY1 CHAR(15);
      SELECT CITY
                  INTO    :CITY1
                  FROM   STAFF
                  WHERE  EMPNAME LIKE 'B__t%';

PROCEDURE SUB31 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0051','pass','MCO');

PROCEDURE SUB32 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0051','fail','MCO');

PROCEDURE SUB33 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB34 SQLCODE;
      INSERT INTO STAFF
                  VALUES('E36','Huyan',36,'Xi_an%');

PROCEDURE SUB35 SQLCODE
    :CITY2 CHAR(15);
      SELECT CITY
                  INTO    :CITY2
                  FROM   STAFF
                  WHERE  CITY LIKE 'XiS___S%%'
                  ESCAPE 'S';

PROCEDURE SUB36 SQLCODE;
      ROLLBACK WORK;

PROCEDURE SUB37 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0052','pass','MCO');

PROCEDURE SUB38 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0052','fail','MCO');

PROCEDURE SUB39 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB40 SQLCODE;
      INSERT INTO STAFF
                  VALUES('E36','Huyan',36,'Xi_an%');

PROCEDURE SUB41 SQLCODE
    :I NUMERIC (9);
      SELECT COUNT(*)
                  INTO  :I
                  FROM   STAFF
                  WHERE  EMPNUM  NOT LIKE '_36';

PROCEDURE SUB42 SQLCODE
    :II NUMERIC (9);
      SELECT COUNT(*)
                  INTO  :II
                  FROM   STAFF
                  WHERE  NOT(EMPNUM  LIKE '_36');

PROCEDURE SUB43 SQLCODE;
      ROLLBACK WORK;

PROCEDURE SUB44 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0053','pass','MCO');

PROCEDURE SUB45 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0053','fail','MCO');

PROCEDURE SUB46 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB47 SQLCODE;
      INSERT INTO STAFF
                  VALUES('E36','Huyan',36,NULL);

PROCEDURE SUB48 SQLCODE
    :EMPNA1 CHAR(20);
      SELECT EMPNAME
                  INTO  :EMPNA1
                  FROM STAFF
                  WHERE CITY IS NULL;

PROCEDURE SUB49 SQLCODE;
      ROLLBACK WORK;

PROCEDURE SUB50 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0054','pass','MCO');

PROCEDURE SUB51 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0054','fail','MCO');

PROCEDURE SUB52 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB53 SQLCODE;
      INSERT INTO STAFF
                  VALUES('E36','Huyan',36,NULL);

PROCEDURE SUB54 SQLCODE
    :I NUMERIC (9);
      SELECT COUNT(*)
                  INTO  :I
                  FROM STAFF;

PROCEDURE SUB55 SQLCODE
    :HOURS1 NUMERIC (9);
      SELECT COUNT(*)
                  INTO  :HOURS1
                  FROM STAFF
                  WHERE CITY IS NOT NULL;

PROCEDURE SUB56 SQLCODE
    :HOURS2 NUMERIC (9);
      SELECT COUNT(*)
                  INTO  :HOURS2
                  FROM STAFF
                  WHERE NOT (CITY IS NULL);

PROCEDURE SUB57 SQLCODE;
      ROLLBACK WORK;

PROCEDURE SUB58 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0055','pass','MCO');

PROCEDURE SUB59 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0055','fail','MCO');

PROCEDURE SUB60 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB61 SQLCODE
    :EMPNA1 CHAR(20);
      SELECT STAFF.EMPNAME
                  INTO    :EMPNA1
                  FROM   STAFF
                  WHERE  NOT EXISTS
                  (  SELECT *
                  FROM   PROJ
                  WHERE  NOT EXISTS
                  ( SELECT *
                  FROM   WORKS
                  WHERE  STAFF.EMPNUM=WORKS.EMPNUM
                  AND    WORKS.PNUM=PROJ.PNUM ));

PROCEDURE SUB62 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0056','pass','MCO');

PROCEDURE SUB63 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0056','fail','MCO');

PROCEDURE SUB64 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB65 SQLCODE
    :CITY1 CHAR(15);
      SELECT CITY
                  INTO    :CITY1
                  FROM   PROJ
                  WHERE  BUDGET > ALL
                  (  SELECT BUDGET
                  FROM   PROJ
                  WHERE  CITY='Vienna' );

PROCEDURE SUB66 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0057','pass','MCO');

PROCEDURE SUB67 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0057','fail','MCO');

PROCEDURE SUB68 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB69 SQLCODE
    :EMPNA1 CHAR(20);
      SELECT EMPNAME
                  INTO    :EMPNA1
                  FROM   STAFF
                  WHERE  GRADE < SOME
                  (  SELECT BUDGET/1000 - 39 
                  FROM   PROJ
                  WHERE  CITY='Deale' );

PROCEDURE SUB70 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0058','pass','MCO');

PROCEDURE SUB71 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0058','fail','MCO');

PROCEDURE SUB72 SQLCODE;
      COMMIT WORK;

PROCEDURE SUB73 SQLCODE
    :EMPNA1 CHAR(20);
      SELECT EMPNAME
                  INTO    :EMPNA1
                  FROM   STAFF
                  WHERE  GRADE < ANY
                  (  SELECT BUDGET/1000 - 39 
                  FROM   PROJ
                  WHERE  CITY='Deale' );

PROCEDURE SUB74 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0059','pass','MCO');

PROCEDURE SUB75 SQLCODE;
      INSERT INTO TESTREPORT
                    VALUES('0059','fail','MCO');

PROCEDURE SUB76 SQLCODE;
      COMMIT WORK;