**************************************************************** * * COMMENT SECTION * * DATE 1989/07/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. * * DML061.PCO * WRITTEN BY: SUN DAJUN * TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE * * THIS ROUTINE TESTS BETWEEN, SET FUNCTIONS, AND INTEGRITY. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 5.12 <between predicate> * SECTION 5.8 <set specification> * SECTION 7.3 <procedure> GR 3b * ****************************************************************
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, dml061.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 TEST0269 *******************
*BETWEEN value expressions in wrong order
DISPLAY"BETWEEN value expressions in wrong order " DISPLAY"Reference X3.135-1989 section 5.12 GR 2 " DISPLAY" ------------------------------------------ " DISPLAY" TEST0269 " DISPLAY" Reference 5.12 General Rules )" DISPLAY" - - - - - - - - - - - - - - - - - - - - - " DISPLAY" " DISPLAY" SELECT COUNT(*) INTO :count1" DISPLAY" FROM WORKS" DISPLAY" WHERE HOURS BETWEEN 80 AND 40;" DISPLAY" INSERT INTO WORKS" DISPLAY" VALUES('E6','P6',-60);" DISPLAY" " DISPLAY" SELECT COUNT(*) INTO :count2" DISPLAY" FROM WORKS" DISPLAY" WHERE HOURS BETWEEN :max1 AND :min1;" DISPLAY" " DISPLAY" SELECT COUNT(*) INTO :count3" DISPLAY" FROM WORKS" DISPLAY" WHERE HOURS BETWEEN :min1 AND :max1;" DISPLAY" " MOVE -1 TO count1 MOVE -1 TO count2 MOVE -1 TO count3 EXECSQLSELECTCOUNT(*) INTO :count1 FROM WORKS
WHERE HOURS BETWEEN 80 AND 40 END-EXEC MOVE SQLCODE TO SQL-COD EXECSQLINSERTINTO WORKS
VALUES('E6','P6',-60) END-EXEC MOVE SQLCODE TO SQL-COD MOVE -40 TO max1 MOVE -80 TO min1 EXECSQLSELECTCOUNT(*) INTO :count2 FROM WORKS
WHERE HOURS BETWEEN :max1 AND :min1 END-EXEC MOVE SQLCODE TO SQL-COD EXECSQLSELECTCOUNT(*) INTO :count3 FROM WORKS
WHERE HOURS BETWEEN :min1 AND :max1 END-EXEC MOVE SQLCODE TO SQL-COD EXECSQL ROLLBACK WORK END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" The correct answer is :" DISPLAY" count1 = 0" DISPLAY" count2 = 0" DISPLAY" count3 = 1" DISPLAY" Your answer is :" DISPLAY" count1 = ", count1 DISPLAY" count2 = ", count2 DISPLAY" count3 = ", count3
if (count1 = 0 AND count2 = 0 AND count3 = 1) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0269','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml061.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0269','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 TEST0269 *******************
******************** BEGIN TEST0270 ******************* *BETWEEN approximate and exact numeric values
DISPLAY"BETWEEN value with comparable data types " DISPLAY"Reference X3.135-1989 section 5.12 SR 1 " DISPLAY" ------------------------------------------ " DISPLAY" TEST0270 " DISPLAY" Reference 5.12 Syntax Rules " DISPLAY" - - - - - - - - - - - - - - - - - - - - - " DISPLAY" " DISPLAY" SELECT COUNT(*) INTO :count1" DISPLAY" FROM WORKS" DISPLAY" WHERE HOURS BETWEEN :ff1 AND :HOURS1 OR
- " " DISPLAY" HOURS BETWEEN 19.999 AND 2.001E1;" DISPLAY" " MOVE -1 TO count1 MOVE 11 TO ff1 MOVE 12 TO HOURS1 EXECSQLSELECTCOUNT(*) INTO :count1 FROM WORKS
WHERE HOURS BETWEEN :ff1 AND :HOURS1 OR
HOURS BETWEEN 19.999 AND 2.001E1 END-EXEC MOVE SQLCODE TO SQL-COD DISPLAY" The correct answer is :" DISPLAY" count1 = 6" DISPLAY" Your answer is :" DISPLAY" count1 = ", count1
if (count1 = 6) then DISPLAY" *** pass *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0270','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml061.pco *** fail *** " EXECSQLINSERTINTO TESTREPORT
VALUES('0270','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 TEST0270 *******************
******************** BEGIN TEST0271 ******************* *COUNT(*) with Cartesian product subset
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.