* Embedded SQL COBOL ("DML073.PCO") translated from * Embedded C on Wed Jan 16 10:18:39 1991.
**************************************************************** * * COMMENT SECTION * * DATE 1989/08/21 EMBEDDED 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. * * DML073.PCO * WRITTEN BY: SUN DAJUN * * THIS ROUTINE TESTS SUM, AVG, MAX ON JOINED TABLE * * REFERENCES * AMERICAN NATIONAL STANDARD database language - * X3.135-1989, 8.10, GR 9 c) * ****************************************************************
MOVE"HU"TO uid CALL"AUTHID"USING uid MOVE"not logged in, not"TO uidx EXECSQLSELECT
USER INTO :uidx FROM HU.ECCO END-EXEC if (uid NOT = uidx) then DISPLAY"ERROR: User " uid " expected." DISPLAY"User " uidx " connected." DISPLAY" " STOPRUN END-IF
MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Embedded COBOL, dml073.pco" DISPLAY" " DISPLAY "59-byte ID" DISPLAY"TEd Version #" DISPLAY" " * date_time print * ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME ******************** BEGIN TEST0393 *******************
EXECSQLSELECTSUM(HOURS), MAX(HOURS) INTO :HOUR1, :HOUR2 FROM STAFF, WORKS END-EXEC MOVE SQLCODE TO SQL-COD
DISPLAY"The correct answer is:" DISPLAY" 2320, 80" DISPLAY"Your answer is:" DISPLAY" ", HOUR1 ", ", HOUR2 if (HOUR1 = 2320 AND HOUR2 = 80) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0393','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml073.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0393','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0393 *******************
******************** BEGIN TEST0394 *******************
DISPLAY" TEST0394 " DISPLAY" AVG, MIN on joined table with WHERE without
- " GROUP" DISPLAY" Reference: ANSI X3.135-1989 5.8 5.21" DISPLAY" - - - - - - - - - - - - - - - - - - - - - - -" DISPLAY" " DISPLAY" SELECT AVG(HOURS), MIN(HOURS)" DISPLAY" INTO :HOUR1, :HOUR2" DISPLAY" FROM STAFF, WORKS" DISPLAY" WHERE STAFF.EMPNUM = 'E2'" DISPLAY" AND STAFF.EMPNUM =
- " WORKS.EMPNUM;"
EXECSQLSELECT AVG(HOURS), MIN(HOURS) INTO :HOUR1, :HOUR2 FROM STAFF, WORKS
WHERE STAFF.EMPNUM = 'E2' AND STAFF.EMPNUM = WORKS.EMPNUM END-EXEC MOVE SQLCODE TO SQL-COD
DISPLAY"The correct answer is:" DISPLAY" 60, 40" DISPLAY"Your answer is:" DISPLAY" ", HOUR1 ", ", HOUR2 if (HOUR1 = 60 AND HOUR2 = 40) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0394','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml073.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0394','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0394 *******************
******************** BEGIN TEST0395 *******************
DISPLAY" TEST0395 " DISPLAY"SUM, MIN on joined table with GROUP without WHERE" DISPLAY" Reference: ANSI X3.135-1989 5.8 5.22" DISPLAY" - - - - - - - - - - - - - - - - - - - - - - -" DISPLAY" " DISPLAY" DECLARE JUPITER CURSOR FOR " DISPLAY" SELECT STAFF.EMPNUM, SUM(HOURS),
- " MIN(HOURS)" DISPLAY" FROM STAFF, WORKS" DISPLAY" GROUP BY STAFF.EMPNUM" DISPLAY" ORDER BY 1;" DISPLAY" "
EXECSQL DECLARE JUPITER CURSOR FOR SELECT STAFF.EMPNUM, SUM(HOURS), MIN(HOURS) FROM STAFF, WORKS
GROUP BY STAFF.EMPNUM ORDERBY 1 END-EXEC
DISPLAY"The correct answer is:" DISPLAY" E1, 464, 12" DISPLAY" E2, 464, 12" DISPLAY" E3, 464, 12" DISPLAY" E4, 464, 12" DISPLAY" E5, 464, 12" DISPLAY"Your answer is:" MOVE 1 TO ifpass EXECSQLOPEN JUPITER END-EXEC MOVE SQLCODE TO SQL-COD EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 NOT = 464 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E1") then MOVE 0 TO ifpass END-IF EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 NOT = 464 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E2") then MOVE 0 TO ifpass END-IF EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 NOT = 464 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E3") then MOVE 0 TO ifpass END-IF EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 NOT = 464 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E4") then MOVE 0 TO ifpass END-IF EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 NOT = 464 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E5") then MOVE 0 TO ifpass END-IF EXECSQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0) then EXECSQLCLOSE JUPITER END-EXEC MOVE SQLCODE TO SQL-COD END-IF if (ifpass = 1) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0395','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml073.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0395','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0395 *******************
******************** BEGIN TEST0396 *******************
DISPLAY" TEST0396 " DISPLAY"SUM, MIN on joined table with WHERE,GROUP
- " BY,HAVING" DISPLAY" Reference: ANSI X3.135-1989 5.8 5.21 5.22 5.23" DISPLAY" - - - - - - - - - - - - - - - - - - - - - - -" DISPLAY" " DISPLAY" DECLARE VENUS CURSOR FOR" DISPLAY" SELECT STAFF.EMPNUM, AVG(HOURS),
- " MIN(HOURS)" DISPLAY" FROM STAFF, WORKS" DISPLAY" WHERE STAFF.EMPNUM IN ('E1','E4','E3')
- " AND" DISPLAY" STAFF.EMPNUM = WORKS.EMPNUM" DISPLAY" GROUP BY STAFF.EMPNUM" DISPLAY" HAVING COUNT(*) > 1" DISPLAY" ORDER BY 1;" DISPLAY" "
EXECSQL DECLARE VENUS CURSOR FOR SELECT STAFF.EMPNUM, AVG(HOURS), MIN(HOURS) FROM STAFF, WORKS
WHERE STAFF.EMPNUM IN ('E1','E4','E3') AND
STAFF.EMPNUM = WORKS.EMPNUM
GROUP BY STAFF.EMPNUM
HAVING COUNT(*) > 1 ORDERBY 1 END-EXEC
DISPLAY"The correct answer is:" DISPLAY" E1, 30 or 31, 12" DISPLAY" E4, 46 or 47, 20" DISPLAY"Your answer is:" MOVE 1 TO ifpass EXECSQLOPEN VENUS END-EXEC MOVE SQLCODE TO SQL-COD EXECSQL FETCH VENUS INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 < 30 OR HOUR1 > 31 OR HOUR2 NOT = 12 OR EMPNO1 NOT = "E1") then MOVE 0 TO ifpass END-IF EXECSQL FETCH VENUS INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" ", EMPNO1 ", ", HOUR1 ", ", HOUR2 if (HOUR1 < 46 OR HOUR1 > 47 OR HOUR2 NOT = 20 OR EMPNO1 NOT = "E4") then MOVE 0 TO ifpass END-IF EXECSQL FETCH VENUS INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE 0 TO ifpass END-IF EXECSQLCLOSE VENUS END-EXEC MOVE SQLCODE TO SQL-COD if (ifpass = 1) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0396','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml073.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0396','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0396 *******************
******************** BEGIN TEST0417 *******************
DISPLAY" TEST0417 " DISPLAY" Cartesian product GROUP BY 2 columns with NULLs" DISPLAY" Reference: ANSI X3.135-1989 5.22 GR 2) 3)" DISPLAY" 5.11 GR 7)" DISPLAY" - - - - - - - - - - - - - - - - - - - - - - -" DISPLAY" " DISPLAY" DECLARE PLUTO CURSOR FOR" DISPLAY" SELECT MAX(STAFF1.GRADE),
- " SUM(STAFF1.GRADE)" DISPLAY" FROM STAFF1, STAFF" DISPLAY" GROUP BY STAFF1.CITY, STAFF.CITY;" DISPLAY" "
MOVE 0 TO ifpass
EXECSQLDELETEFROM STAFF1 END-EXEC MOVE SQLCODE TO SQL-COD
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.