IDENTIFICATION DIVISION.
PROGRAM-ID. DML073.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* 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)
*
****************************************************************
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 EMPNO1 PIC X(3).
01 HOUR1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 HOUR2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 GRD1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 GRD2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 AVG1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 AVG2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SUM1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 CNT1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 MAX1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 MIN1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 uid PIC X(18).
01 uidx PIC X(18).
EXEC SQL END DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 ifpass PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 lpcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SQL-COD PIC S9(9) 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
EXEC SQL SELECT
USER INTO :uidx FROM HU.ECCO END-EXEC
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, Embedded COBOL, dml073.pco"
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 TEST0393 *******************
DISPLAY " TEST0393 "
DISPLAY " SUM, MAX on Cartesian product"
DISPLAY " Reference: ANSI X3.135-1989 5.8"
DISPLAY " 5.20 GR 2b"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - - -"
DISPLAY " "
DISPLAY " SELECT SUM(HOURS), MAX(HOURS)"
DISPLAY " INTO :HOUR1, :HOUR2"
DISPLAY " FROM STAFF, WORKS;"
EXEC SQL SELECT SUM(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 *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0393','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0393','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL 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;"
EXEC SQL SELECT 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 *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0394','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0394','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL 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 " "
EXEC SQL DECLARE JUPITER CURSOR FOR
SELECT STAFF.EMPNUM, SUM(HOURS), MIN(HOURS)
FROM STAFF, WORKS
GROUP BY STAFF.EMPNUM
ORDER BY 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
EXEC SQL OPEN JUPITER END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL 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
EXEC SQL 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
EXEC SQL 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
EXEC SQL 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
EXEC SQL 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
EXEC SQL FETCH JUPITER INTO :EMPNO1, :HOUR1, :HOUR2
END-EXEC
MOVE SQLCODE TO SQL-COD
if (SQLCODE NOT = 0) then
EXEC SQL CLOSE JUPITER END-EXEC
MOVE SQLCODE TO SQL-COD
END-IF
if (ifpass = 1) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0395','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0395','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL 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 " "
EXEC SQL 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
ORDER BY 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
EXEC SQL OPEN VENUS END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL 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
EXEC SQL 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
EXEC SQL FETCH VENUS INTO :EMPNO1, :HOUR1, :HOUR2 END-EXEC
MOVE SQLCODE TO SQL-COD
if (SQLCODE = 0) then
MOVE 0 TO ifpass
END-IF
EXEC SQL CLOSE VENUS END-EXEC
MOVE SQLCODE TO SQL-COD
if (ifpass = 1) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0396','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0396','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL 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
EXEC SQL DELETE FROM STAFF1 END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO STAFF
VALUES ('E6', 'David', 17, NULL) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO STAFF
VALUES ('E7', 'Tony', 18, NULL) END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL INSERT INTO STAFF1
SELECT * FROM STAFF END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL DECLARE PLUTO CURSOR FOR
SELECT MAX(STAFF1.GRADE), SUM(STAFF1.GRADE)
FROM STAFF1, STAFF
GROUP BY STAFF1.CITY, STAFF.CITY END-EXEC
DISPLAY "For groups containing MAX(GRADE) = 18,"
DISPLAY "the correct answer is:"
DISPLAY " 18, 35"
DISPLAY " 18, 70"
DISPLAY " 18, 70"
DISPLAY " 18, 70"
DISPLAY "Your answer is:"
EXEC SQL OPEN PLUTO END-EXEC
MOVE SQLCODE TO SQL-COD
MOVE 1 TO lpcnt
PERFORM P50 UNTIL lpcnt > 19
EXEC SQL CLOSE PLUTO END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
if (ifpass = 301) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0417','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0417','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0417 *******************
******************** BEGIN TEST0418 *******************
DISPLAY " TEST0418 "
DISPLAY " AVG, SUM, COUNT on Cartesian product with NULL"
DISPLAY " Reference: ANSI X3.135-1989 5.8 GR 2)"
DISPLAY " 5.9 GR 1)"
DISPLAY " 5.11 GR 7)"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - - -"
DISPLAY " "
DISPLAY " SELECT AVG(T1.COL4), AVG(T1.COL4 +
- " T2.COL4),"
DISPLAY " SUM(T2.COL4), COUNT(DISTINCT
- " T1.COL4)"
DISPLAY " INTO :AVG1, :AVG2, :SUM1, :CNT1"
DISPLAY " FROM VTABLE T1, VTABLE T2;"
DISPLAY " "
EXEC SQL SELECT AVG(T1.COL4), AVG(T1.COL4 + T2.COL4),
SUM(T2.COL4), COUNT(DISTINCT T1.COL4)
INTO :AVG1, :AVG2, :SUM1, :CNT1
FROM VTABLE T1, VTABLE T2 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "The correct answer is:"
DISPLAY " 147 or 148, 295 or 296, 1772, 3"
DISPLAY "Your answer is:"
DISPLAY " ", AVG1 ", ", AVG2 ", ", SUM1 ", ", CNT1
MOVE 1 TO ifpass
if (CNT1 NOT = 3 OR SUM1 NOT = 1772) then
MOVE 0 TO ifpass
END-IF
if (AVG1 < 147 OR AVG1 > 148) then
MOVE 0 TO ifpass
END-IF
if (AVG2 < 295 OR AVG2 > 296) then
MOVE 0 TO ifpass
END-IF
if (ifpass = 1) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0418','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0418','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0418 *******************
******************** BEGIN TEST0419 *******************
DISPLAY " TEST0419 "
DISPLAY " SUM,MAX,MIN on joined table view "
DISPLAY " Reference: ANSI X3.135-1989 5.8 GR 2)"
DISPLAY " 6.9 GR 1)"
DISPLAY " - - - - - - - - - - - - - - - - - - - - - - -"
DISPLAY " "
DISPLAY " SELECT SUM(COST), MAX(COST), MIN(COST)"
DISPLAY " INTO :SUM1, :MAX1, :MIN1"
DISPLAY " FROM STAFF_WORKS_DESIGN;"
DISPLAY " "
EXEC SQL SELECT SUM(COST), MAX(COST), MIN(COST)
INTO :SUM1, :MAX1, :MIN1
FROM STAFF_WORKS_DESIGN END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "The correct answer is:"
DISPLAY " 3488, 960, 288"
DISPLAY "Your answer is:"
DISPLAY " ", SUM1 ", ", MAX1 ", ", MIN1
MOVE 1 TO ifpass
if (SUM1 NOT = 3488) then
MOVE 0 TO ifpass
END-IF
if (MAX1 NOT = 960) then
MOVE 0 TO ifpass
END-IF
if (MIN1 NOT = 288) then
MOVE 0 TO ifpass
END-IF
if (ifpass = 1) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0419','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml073.pco *** fail *** "
EXEC SQL INSERT INTO TESTREPORT
VALUES('0419','fail','PCO') END-EXEC
ADD 1 TO errcnt
MOVE SQLCODE TO SQL-COD
END-IF
DISPLAY
"===================================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0419 *******************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
STOP RUN.
* **** Procedures for PERFORM statements
P50.
MOVE 0 TO GRD1
EXEC SQL FETCH PLUTO INTO :GRD1, :GRD2 END-EXEC
MOVE SQLCODE TO SQL-COD
if (GRD1 = 18 AND GRD2 = 35) then
COMPUTE ifpass = ifpass + 1
DISPLAY " ", GRD1 ", ", GRD2
END-IF
if (GRD1 = 18 AND GRD2 = 70) then
COMPUTE ifpass = ifpass + 100
DISPLAY " ", GRD1 ", ", GRD2
END-IF
ADD 1 TO lpcnt
.
¤ Dauer der Verarbeitung: 0.41 Sekunden
(vorverarbeitet)
¤
|
Haftungshinweis
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.
|