Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma


products/sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 10 kB

Quelle dml114.mco Sprache: unbekannt

-- SQL Test Suite, V6.0, SQL Module dml114.mco
-- 59-byte ID
--
MODULE DML114
LANGUAGE COBOL
AUTHORIZATION FLATER

DECLARE C11411  CURSOR FOR
               SELECT COUNT(*) FROM WORKWEEK
               GROUP BY HOURS
DECLARE JOANS  CURSOR FOR
               SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS
               WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM
               AND EMPNAME > 'Betty'
               ORDER BY EMPNAME
DECLARE C63931  CURSOR FOR
               SELECT EMPNUM as WhatsHisNumber, GRADE, CITY
               FROM Flater.staff FLaterStaff_Flater
               WHERE EMPNAME > 'Betty'
               ORDER BY WHATSHISNUMBER

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

PROCEDURE SUB2 SQLCODE SQLSTATE;
      ROLLBACK WORK;

PROCEDURE SUB3 SQLCODE SQLSTATE;
      CREATE VIEW WORKWEEK AS
                    SELECT EMPNUM, HOURS FROM HU.WORKS
                    GROUP BY HOURS, EMPNUM;

PROCEDURE SUB4 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB5 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :INT1 NUMERIC (9);
      SELECT EMPNUM, SUM (HOURS) INTO :CH1, :INT1
                    FROM WORKWEEK
                    WHERE HOURS > 20
                    GROUP BY EMPNUM
                    HAVING EMPNUM = 'E1';

PROCEDURE SUB6 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM WORKWEEK WHERE HOURS > 40;

PROCEDURE SUB7 SQLCODE SQLSTATE
    :CH2 CHAR(20);
      SELECT EMPNAME INTO :CH2
                    FROM HU.STAFF, WORKWEEK
                    WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM
                    AND HOURS = 12;

PROCEDURE SUB8 SQLCODE SQLSTATE
    :INT1 NUMERIC (9)
    :CH1 CHAR(3)
    :CH4 CHAR(3)
    :INT2 NUMERIC (9);
      SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM),
                  AVG(HOURS)
                    INTO :INT1, :CH1, :CH4, :INT2
                    FROM WORKWEEK;

PROCEDURE SUB9 SQLCODE SQLSTATE
    :CH2 CHAR(20);
      SELECT EMPNAME INTO :CH2
                    FROM HU.STAFF WHERE EMPNUM =
                    (SELECT EMPNUM FROM WORKWEEK
                    WHERE HOURS = 12);

PROCEDURE SUB10 SQLCODE SQLSTATE
    :CH2 CHAR(20);
      SELECT EMPNAME INTO :CH2
                    FROM HU.STAFF WHERE EMPNUM =
                    (SELECT EMPNUM FROM HU.WORKS
                    GROUP BY EMPNUM, HOURS
                    HAVING HOURS = 12);

PROCEDURE SUB11 SQLCODE SQLSTATE;
    OPEN C11411;

PROCEDURE SUB12 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      FETCH C11411 INTO :INT1;

PROCEDURE SUB13 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB14 SQLCODE SQLSTATE;
      DROP VIEW WORKWEEK CASCADE;

PROCEDURE SUB15 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB16 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0635','pass','MCO');

PROCEDURE SUB17 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0635','fail','MCO');

PROCEDURE SUB18 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB19 SQLCODE SQLSTATE;
      CREATE VIEW QUALSTAR AS
                    SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS
                    WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM;

PROCEDURE SUB20 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB21 SQLCODE SQLSTATE;
      CREATE VIEW CORRQUALSTAR AS
                    SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS
                    WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM;

PROCEDURE SUB22 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB23 SQLCODE SQLSTATE;
      CREATE VIEW SUBQ2 AS
                    SELECT DISTINCT * FROM QUALSTAR;

PROCEDURE SUB24 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB25 SQLCODE SQLSTATE;
      CREATE VIEW CORRSUBQ2 AS
                    SELECT DISTINCT * FROM CORRQUALSTAR;

PROCEDURE SUB26 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB27 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM QUALSTAR;

PROCEDURE SUB28 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM SUBQ2;

PROCEDURE SUB29 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15)
    :INT2 NUMERIC (9);
      SELECT EMPNUM, GRADE, CITY, HOURS
                    INTO :CH1, :INT1, :CH3, :INT2
                    FROM QUALSTAR WHERE EMPNAME = 'Carmen';

PROCEDURE SUB30 SQLCODE SQLSTATE;
    OPEN JOANS;

PROCEDURE SUB31 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :CH2 CHAR(20)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15)
    :INT2 NUMERIC (9);
      FETCH JOANS INTO :CH1, :CH2, :INT1, :CH3, :INT2
                 ;

PROCEDURE SUB32 SQLCODE SQLSTATE;
      CLOSE JOANS;

PROCEDURE SUB33 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :CH2 CHAR(20)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15)
    :INT2 NUMERIC (9);
      SELECT HU.STAFF.*, HOURS
                    INTO :CH1, :CH2, :INT1, :CH3, :INT2
                    FROM HU.STAFF, HU.WORKS
                    WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM
                    AND EMPNAME = 'Carmen';

PROCEDURE SUB34 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM CORRQUALSTAR
                 ;

PROCEDURE SUB35 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1 FROM CORRSUBQ2;

PROCEDURE SUB36 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15)
    :INT2 NUMERIC (9);
      SELECT EMPNUM, GRADE, CITY, HOURS
                    INTO :CH1, :INT1, :CH3, :INT2
                    FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen';

PROCEDURE SUB37 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB38 SQLCODE SQLSTATE;
      DROP VIEW QUALSTAR CASCADE;

PROCEDURE SUB39 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB40 SQLCODE SQLSTATE;
      DROP VIEW CORRQUALSTAR CASCADE;

PROCEDURE SUB41 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB42 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0637','pass','MCO');

PROCEDURE SUB43 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0637','fail','MCO');

PROCEDURE SUB44 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB45 SQLCODE SQLSTATE;
      create view Staff (Empnum, empname, Grade, City)
                  as
                    select empnum, EMPNAME, Grade, cItY from  Hu.Staff
                 ;

PROCEDURE SUB46 SQLCODE SQLSTATE;
      commit work;

PROCEDURE SUB47 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT Count(*) INTO :INT1 FROM STAFF;

PROCEDURE SUB48 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15);
      SELECT EMPNUM as WhatsHisNumber, GRADE, CITY
                    INTO :CH1, :INT1, :CH3
                    FROM Flater.staff FLaterStaff_Flater
                    WHERE EMPNAME = 'Carmen'
                    AND FLATERstaff_fLATER.whatshisnumber = 'E3';

PROCEDURE SUB49 SQLCODE SQLSTATE;
    OPEN C63931;

PROCEDURE SUB50 SQLCODE SQLSTATE
    :CH1 CHAR(3)
    :INT1 NUMERIC (9)
    :CH3 CHAR(15);
      FETCH c63931 INTO :CH1, :INT1, :CH3;

PROCEDURE SUB51 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB52 SQLCODE SQLSTATE;
      DROP VIEW STAFF CASCADE;

PROCEDURE SUB53 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE sub54 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0639','pass','MCO');

PROCEDURE SUB55 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0639','fail','MCO');

PROCEDURE SUB56 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB57 SQLCODE SQLSTATE;
      CREATE TABLE FEAT16 (
                    EMPNUM INT PRIMARY KEY,
                    PNUM   INT UNIQUE);

PROCEDURE SUB58 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB59 SQLCODE SQLSTATE;
      CREATE TABLE BARNO (
                    P1 INT, P2 CHAR, X1 INT, X2 CHAR,
                    UNIQUE (X2, X1),
                    PRIMARY KEY (P1, P2));

PROCEDURE SUB60 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB61 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (1, 10);

PROCEDURE SUB62 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (2, 20);

PROCEDURE SUB63 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (1, 30);

PROCEDURE SUB64 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (3, 20);

PROCEDURE SUB65 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (3, NULL);

PROCEDURE SUB66 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (4, NULL);

PROCEDURE SUB67 SQLCODE SQLSTATE;
      INSERT INTO FEAT16 VALUES (5, NULL);

PROCEDURE SUB68 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (1, 'A', 10, 'a')
                 ;

PROCEDURE SUB69 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (2, 'A', 20, 'a')
                 ;

PROCEDURE SUB70 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (1, 'A', 30, 'a')
                 ;

PROCEDURE SUB71 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (3, 'A', 20, 'a')
                 ;

PROCEDURE SUB72 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (3, NULL, 30, 'a')
                 ;

PROCEDURE SUB73 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (3, NULL, 30, 'b')
                 ;

PROCEDURE SUB74 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (3, 'A', 30, NULL)
                 ;

PROCEDURE SUB75 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (3, 'B', 30, NULL)
                 ;

PROCEDURE SUB76 SQLCODE SQLSTATE;
      INSERT INTO BARNO VALUES (4, 'B', NULL, NULL)
                 ;

PROCEDURE SUB77 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB78 SQLCODE SQLSTATE;
      DROP TABLE FEAT16 CASCADE;

PROCEDURE SUB79 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB80 SQLCODE SQLSTATE;
      DROP TABLE BARNO CASCADE;

PROCEDURE SUB81 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB82 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0641','pass','MCO');

PROCEDURE SUB83 SQLCODE SQLSTATE;
      INSERT INTO HU.TESTREPORT
                      VALUES('0641','fail','MCO');

PROCEDURE SUB84 SQLCODE SQLSTATE;
      COMMIT WORK;