| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 15 kB |
|
Quelle dml018.cob Sprache: Cobol |
|||||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML018. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML018.SCO") calling SQL * procedures in file "DML018.MCO" **************************************************************** * * COMMENT SECTION * * DATE 1987/08/21 STANDARD COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * DML018.SCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE HAVING CLAUSE IN THE SQL LANGUAGE. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 5.23 <having clause> * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 WEMP PIC X(3). 01 WPNO1 PIC X(3). 01 HOURS1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * EXEC SQL END DECLARE SECTION END-EXEC 01 i PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ii PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE. * date_time declaration * 01 TO-DAY PIC 9(6). 01 THE-TIME PIC 9(8). PROCEDURE DIVISION. P0. MOVE "HU" TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx CALL "AUTHCK" USING SQLCODE uidx MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY "ERROR: User " uid " expected." DISPLAY "User " uidx " connected." DISPLAY " " STOP RUN END-IF MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Module COBOL, dml018.sco" DISPLAY " " DISPLAY "59-byte ID" DISPLAY "TEd Version #" DISPLAY " " * date_time print * ACCEPT TO-DAY FROM DATE ACCEPT THE-TIME FROM TIME DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME ******************** BEGIN TEST0069 ******************* DISPLAY " " DISPLAY " TEST0069 " DISPLAY " References: section 5.23 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY "*** Select group by a single column and having the - " count" DISPLAY "*** of the column larger than a certain value." DISPLAY " *** SELECT PNUM" DISPLAY " *** FROM WORKS" DISPLAY " *** WHERE PNUM > 'P1' " DISPLAY " *** GROUP BY PNUM" DISPLAY " *** HAVING COUNT(*) > 1" * EXEC SQL DECLARE WORKS1 CURSOR FOR * SELECT PNUM * FROM WORKS * WHERE PNUM > 'P1' * GROUP BY PNUM * HAVING COUNT(*) > 1 END-EXEC * EXEC SQL OPEN WORKS1; CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i MOVE 1 TO ii PERFORM P50 UNTIL ii > 19 * EXEC SQL CLOSE WORKS1; CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " i should be 3; i = ", i " " if (i = 3) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0069','pass','MCO') END-EXEC CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml018.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0069','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "======================================================" * EXEC SQL COMMIT WORK; CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0069 ******************* ******************** BEGIN TEST0070 ******************* DISPLAY " " DISPLAY " TEST0070 " DISPLAY " References: section 5.23 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT PNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** GROUP BY PNUM " DISPLAY " *** HAVING COUNT(*) > 2 " * EXEC SQL DECLARE WORKS3 CURSOR FOR * SELECT PNUM * FROM WORKS * GROUP BY PNUM * HAVING COUNT(*) > 2 END-EXEC * EXEC SQL OPEN WORKS3; CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i MOVE 1 TO ii PERFORM P49 UNTIL ii > 19 * EXEC SQL CLOSE WORKS3; CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD if (i = 1 AND WPNO1 = "P2") then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0070','pass','MCO') END-EXEC CALL "SUB8" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml018.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0070','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "======================================================" * EXEC SQL COMMIT WORK; CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0070 ******************* ******************** BEGIN TEST0071 ******************* DISPLAY " " DISPLAY " TEST0071 " DISPLAY " References: section 5.23 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY "*** Select group by all the columns in WORKS and - " having HOURS" DISPLAY "*** between a certain range." DISPLAY " *** SELECT EMPNUM, PNUM, HOURS" DISPLAY " *** FROM WORKS" DISPLAY " *** GROUP BY PNUM,EMPNUM,HOURS" DISPLAY " *** HAVING MIN(HOURS) > 12 AND MAX(HOURS) < - " 80" * EXEC SQL DECLARE WORKS4 CURSOR FOR * SELECT EMPNUM, PNUM, HOURS * FROM WORKS * GROUP BY PNUM,EMPNUM,HOURS * HAVING MIN(HOURS) > 12 AND MAX(HOURS) < 80 END-EXEC * This query satisfies the 5.25 SR 7), becuase table WORKS * only have three columns which are in GROUP BY clause. * see 5.25 SR 4). about <select list> "*". * EXEC SQL OPEN WORKS4; CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNUM PNUM HOURS " END-IF MOVE 1 TO ii PERFORM P48 UNTIL ii > 19 * EXEC SQL CLOSE WORKS4; CALL "SUB12" USING SQLCODE MOVE SQLCODE TO SQL-COD if (i = 7) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0071','pass','MCO') END-EXEC CALL "SUB13" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml018.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0071','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "======================================================" * EXEC SQL COMMIT WORK; CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0071 ******************* ******************** BEGIN TEST0072 ******************* DISPLAY " " DISPLAY " TEST0072 " DISPLAY " References: section 5.23 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT WORKS.PNUM" DISPLAY " *** FROM WORKS" DISPLAY " *** GROUP BY WORKS.PNUM" DISPLAY " *** HAVING WORKS.PNUM IN ( SELECT PROJ.PNUM" DISPLAY " *** FROM PROJ" DISPLAY " *** GROUP BY PROJ.PNUM" DISPLAY " *** HAVING SUM(PROJ.BUDGET) > - " 25000 )" * EXEC SQL DECLARE WORKS5 CURSOR FOR * SELECT WORKS.PNUM * FROM WORKS * GROUP BY WORKS.PNUM * HAVING WORKS.PNUM IN ( SELECT PROJ.PNUM * FROM PROJ * GROUP BY PROJ.PNUM * HAVING SUM(PROJ.BUDGET) > 25000 ) END-EXEC * EXEC SQL OPEN WORKS5; CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i MOVE 1 TO ii PERFORM P47 UNTIL ii > 19 * EXEC SQL CLOSE WORKS5; CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " i should be 3; i = ", i " " if (i = 3) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0072','pass','MCO') END-EXEC CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml018.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0072','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB19" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "======================================================" * EXEC SQL COMMIT WORK; CALL "SUB20" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0072 ******************* ******************** BEGIN TEST0073 ******************* DISPLAY " " DISPLAY " TEST0073 " DISPLAY " References: section 5.23 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT SUM(HOURS) " DISPLAY " *** FROM WORKS " DISPLAY " *** HAVING MIN(PNUM) > 'P0' " * EXEC SQL DECLARE WORKS6 CURSOR FOR * SELECT SUM(HOURS) * FROM WORKS * HAVING MIN(PNUM) > 'P0' END-EXEC * EXEC SQL OPEN WORKS6; CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i MOVE 1 TO ii PERFORM P46 UNTIL ii > 19 * EXEC SQL CLOSE WORKS6; CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " i should be 1; i = ", i " " if (i = 1 AND HOURS1 = 464) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0073','pass','MCO') END-EXEC CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml018.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0073','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "======================================================" * EXEC SQL COMMIT WORK; CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0073 ******************* **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements P50. * EXEC SQL FETCH WORKS1 INTO * :WPNO1 END-EXEC CALL "SUB26" USING SQLCODE WPNO1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then COMPUTE i = i + 1 DISPLAY " PNUM = ", WPNO1 END-IF ADD 1 TO ii . P49. * EXEC SQL FETCH WORKS3 INTO * :WPNO1 END-EXEC CALL "SUB27" USING SQLCODE WPNO1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then COMPUTE i = i + 1 DISPLAY " PNUM = ", WPNO1 END-IF ADD 1 TO ii . P48. * EXEC SQL FETCH WORKS4 INTO * :WEMP,:WPNO1,:HOURS1 END-EXEC CALL "SUB28" USING SQLCODE WEMP WPNO1 HOURS1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then COMPUTE i = i + 1 DISPLAY WEMP " ", WPNO1 " ", HOURS1 END-IF ADD 1 TO ii . P47. * EXEC SQL FETCH WORKS5 INTO * :WPNO1 END-EXEC CALL "SUB29" USING SQLCODE WPNO1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then COMPUTE i = i + 1 DISPLAY " PNUM = ", WPNO1 END-IF ADD 1 TO ii . P46. * EXEC SQL FETCH WORKS6 INTO * :HOURS1 END-EXEC CALL "SUB30" USING SQLCODE HOURS1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then COMPUTE i = i + 1 DISPLAY " SUM of the HOURS1s = ", HOURS1 END-IF ADD 1 TO ii . ¤ Dauer der Verarbeitung: 0.13 Sekunden (vorverarbeitet) ¤*© Formatika GbR, Deutschland |
|
2026-03-28