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 6 kB

Quelle dml108.mco Sprache: unbekannt

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

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 TABLE TEMPS (
                    ENTERED  TIMESTAMP,
                    START    DATE,
                    APPT     INTERVAL DAY,
                    HOUR_IN  TIME,
                    HOUR_OUT TIME,
                    LUNCH    INTERVAL HOUR TO MINUTE);

PROCEDURE SUB4 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB5 SQLCODE SQLSTATE;
      CREATE VIEW SUBQ1 AS
                    SELECT MIN (HOUR_IN) AS TOO_EARLY,
                    MAX (ALL START) AS LATEST,
                    AVG (LUNCH) AS AVGLUNCH,
                    AVG (DISTINCT LUNCH) AS D_AVGLUNCH,
                    SUM (APPT) AS SUMAPPT
                    FROM TEMPS;

PROCEDURE SUB6 SQLCODE SQLSTATE;
      COMMIT WORK;

PROCEDURE SUB7 SQLCODE SQLSTATE;
      INSERT INTO TEMPS VALUES (
                    TIMESTAMP '1993-11-10 12:25:14',
                    DATE '1993-11-12',
                    INTERVAL '4' DAY,
                    TIME '08:30:00',
                    TIME '16:30:00',
                    INTERVAL '1:00' HOUR TO MINUTE);

PROCEDURE SUB8 SQLCODE SQLSTATE;
      INSERT INTO TEMPS VALUES (
                    TIMESTAMP '1993-11-10 13:15:14',
                    DATE '1993-11-15',
                    INTERVAL '5' DAY,
                    TIME '08:30:00',
                    TIME '17:30:00',
                    INTERVAL '0:30' HOUR TO MINUTE);

PROCEDURE SUB9 SQLCODE SQLSTATE;
      INSERT INTO TEMPS VALUES (
                    TIMESTAMP '1993-11-17 09:56:48',
                    DATE '1994-11-18',
                    INTERVAL '3' DAY,
                    TIME '09:00:00',
                    TIME '17:00:00',
                    INTERVAL '1:00' HOUR TO MINUTE);

PROCEDURE SUB10 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    LUNCH < INTERVAL '1:00' HOUR TO MINUTE;

PROCEDURE SUB11 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    LUNCH <= INTERVAL '1:00' HOUR TO MINUTE;

PROCEDURE SUB12 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    START <> DATE '1993-11-15' AND
                    START <> DATE '1993-11-12';

PROCEDURE SUB13 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    START = DATE '1993-11-15' OR
                    START = DATE '1993-11-12';

PROCEDURE SUB14 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    HOUR_OUT > TIME '17:00:00';

PROCEDURE SUB15 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    HOUR_OUT >= TIME '17:00:00';

PROCEDURE SUB16 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    ENTERED BETWEEN TIMESTAMP '1993-11-10 00:00:00' AND
                    TIMESTAMP '1993-11-10 23:59:59';

PROCEDURE SUB17 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    HOUR_OUT IN
                    (SELECT HOUR_IN + INTERVAL '8' HOUR FROM TEMPS);

PROCEDURE SUB18 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    (START, APPT) OVERLAPS
                    (DATE '1993-11-14', INTERVAL '2' DAY);

PROCEDURE SUB19 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    HOUR_OUT = ANY
                    (SELECT HOUR_IN + INTERVAL '8' HOUR FROM TEMPS);

PROCEDURE SUB20 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    EXTRACT (YEAR FROM ENTERED) <> SOME
                    (SELECT EXTRACT (YEAR FROM START)
                    FROM TEMPS);

PROCEDURE SUB21 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT(*) INTO :INT1
                    FROM TEMPS WHERE
                    EXTRACT (YEAR FROM START) <> ALL
                    (SELECT EXTRACT (YEAR FROM ENTERED)
                    FROM TEMPS);

PROCEDURE SUB22 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT EXTRACT (HOUR FROM TOO_EARLY)
                    * 100 + EXTRACT (MINUTE FROM TOO_EARLY)
                    INTO :INT1 FROM SUBQ1;

PROCEDURE SUB23 SQLCODE SQLSTATE
    :INT1 NUMERIC (9)
    :INT2 NUMERIC (9);
      SELECT EXTRACT (YEAR FROM LATEST),
                    EXTRACT (MONTH FROM LATEST) * 100 +
                    EXTRACT (DAY FROM LATEST)
                    INTO :INT1, :INT2 FROM SUBQ1;

PROCEDURE SUB24 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT EXTRACT (HOUR FROM AVGLUNCH)
                    * 100 + EXTRACT (MINUTE FROM AVGLUNCH)
                    INTO :INT1 FROM SUBQ1;

PROCEDURE SUB25 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT EXTRACT (HOUR FROM D_AVGLUNCH)
                    * 100 + EXTRACT (MINUTE FROM D_AVGLUNCH)
                    INTO :INT1 FROM SUBQ1;

PROCEDURE SUB26 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT EXTRACT (DAY FROM SUMAPPT)
                    INTO :INT1 FROM
                    SUBQ1;

PROCEDURE SUB27 SQLCODE SQLSTATE
    :INT1 NUMERIC (9);
      SELECT COUNT (DISTINCT LUNCH) INTO :INT1 FROM
                  TEMPS;

PROCEDURE SUB28 SQLCODE SQLSTATE;
      ROLLBACK WORK;

PROCEDURE SUB29 SQLCODE SQLSTATE;
      DROP TABLE TEMPS CASCADE;

PROCEDURE SUB30 SQLCODE SQLSTATE;
      COMMIT WORK;

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

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

PROCEDURE SUB33 SQLCODE SQLSTATE;
      COMMIT WORK;