|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML147.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. xyz.
OBJECT-COMPUTER. xyz.
DATA DIVISION.
WORKING-STORAGE SECTION.
* EMBEDDED COBOL (file "DML147.PCO")
****************************************************************
*
* COMMENT SECTION
*
* DATE 1995/2/1 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.
*
* DML147.PCO
* WRITTEN BY: Joan Sullivan (mostly)
* TRANSLATED AUTOMATICALLY FROM EMBEDDED C BY CHRIS SCHANZLE
* Embedded COBOL by David Flater
*
* This routine tests (1) the rolling back of schema
* manipulation, and (2) queries involving joins.
*
* REFERENCES
* FIPS PUB 127-2 14.1 Transitional SQL
* ANSI SQL-1992
*
****************************************************************
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 SQLCODE PIC S9(9) COMP.
01 SQLSTATE PIC X(5).
01 uid PIC X(18).
01 uidx PIC X(18).
01 en PIC X(3).
01 ename PIC X(20).
01 ename2 PIC X(20).
01 ct PIC X(15).
01 sl PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 gr PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 indic1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 indic2 PIC S9(4) DISPLAY SIGN LEADING SEPARATE.
01 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
EXEC SQL END DECLARE SECTION END-EXEC
01 norm1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 norm2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 ALPNUM-TABLE VALUE IS
"01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ".
05 ALPNUM PIC X OCCURS 36 TIMES.
01 NORMSQ.
05 NORMSQX PIC X OCCURS 5 TIMES.
01 errcnt PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
*date_time declaration
01 TO-DAY PIC 9(6).
01 THE-TIME PIC 9(8).
01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE.
PROCEDURE DIVISION.
P0.
MOVE "FLATER " TO uid
CALL "AUTHID" USING uid
MOVE "not logged in, not" TO uidx
EXEC SQL SELECT USER INTO :uidx FROM HU.ECCO END-EXEC
MOVE SQLCODE TO SQL-COD
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
if (uid NOT = uidx) then
DISPLAY "ERROR: User ", uid " expected. User ", uidx "
- " connected"
STOP RUN
END-IF
MOVE 0 TO errcnt
DISPLAY
"SQL Test Suite, V6.0, Embedded COBOL, dml147.pco"
DISPLAY
"59-byte ID"
DISPLAY "TEd Version #"
*date_time print
ACCEPT TO-DAY FROM DATE
ACCEPT THE-TIME FROM TIME
DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST0840 *******************
MOVE 1 TO flag
DISPLAY " TEST0840 "
DISPLAY " Roll back schema manipulation"
DISPLAY "References:"
DISPLAY " F# 3 -- Basic schema manipulation"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE NOT_THERE (C1 CHAR (10));"
EXEC SQL CREATE TABLE NOT_THERE (C1 CHAR (10)) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*14.4 GR.3: Any changes to schema are cancelled by a rollback
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "INSERT INTO NOT_THERE VALUES ('1234567890');"
EXEC SQL INSERT INTO NOT_THERE VALUES ('1234567890')
END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "CREATE VIEW NOT_HERE AS"
DISPLAY " SELECT * FROM USIG;"
EXEC SQL CREATE VIEW NOT_HERE AS
SELECT * FROM USIG END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM NOT_HERE;"
EXEC SQL SELECT COUNT (*) INTO :int1
FROM NOT_HERE END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ALTER TABLE USIG"
DISPLAY " ADD COLUMN NUL INT;"
EXEC SQL ALTER TABLE USIG
ADD COLUMN NUL INT END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*This is a syntax error. If it is rejected at compile time,
*this portion of the test is passed.
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM USIG WHERE NUL IS NULL;"
EXEC SQL SELECT COUNT (*) INTO :int1
FROM USIG WHERE NUL IS NULL END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be < 0; its value is ", SQL-COD
if (SQLCODE NOT < 0) then
MOVE 0 TO flag
END-IF
DISPLAY "SQLSTATE should be 42000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (NORMSQ NOT = "42000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "42000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE USIG CASCADE;"
EXEC SQL DROP TABLE USIG CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 50 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM U_SIG;"
EXEC SQL SELECT COUNT(*) INTO :int1
FROM U_SIG END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 2; its value is ", int1
if (int1 NOT = 2) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE 50 TO int1
DISPLAY "SELECT COUNT(*) INTO :int1"
DISPLAY " FROM USIG;"
EXEC SQL SELECT COUNT(*) INTO :int1
FROM USIG END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 2; its value is ", int1
if (int1 NOT = 2) then
MOVE 0 TO flag
END-IF
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0840','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.pco *** fail *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0840','fail','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0840 ********************
******************** BEGIN TEST0841 *******************
MOVE 1 TO flag
DISPLAY " TEST0841 "
DISPLAY " Multiple-join and default order of joins"
DISPLAY "References:"
DISPLAY " F# 4 -- Joined table"
DISPLAY " 3.3.4.4 PP.2 -- Order of evaluation"
DISPLAY " 7.5 -- Format of allows parens"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "DELETE FROM HU.STAFF4;"
EXEC SQL DELETE FROM HU.STAFF4 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "INSERT INTO HU.STAFF4"
DISPLAY " SELECT * FROM HU.STAFF3"
DISPLAY " WHERE EMPNUM > 'E3';"
EXEC SQL INSERT INTO HU.STAFF4
SELECT * FROM HU.STAFF3
WHERE EMPNUM > 'E3' END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*testing that joins are done left to right
*same as next query where parentheses are explicit
DISPLAY "DECLARE C14721 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM HU.STAFF3"
DISPLAY " NATURAL LEFT JOIN HU.STAFF"
DISPLAY " NATURAL INNER JOIN HU.STAFF4"
DISPLAY " ORDER BY EMPNUM DESC;"
EXEC SQL DECLARE C14721 CURSOR FOR
SELECT EMPNUM FROM HU.STAFF3
NATURAL LEFT JOIN HU.STAFF
NATURAL INNER JOIN HU.STAFF4
ORDER BY EMPNUM DESC END-EXEC
DISPLAY "OPEN C14721;"
EXEC SQL OPEN C14721 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14721 INTO :en;"
EXEC SQL FETCH C14721 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14721 INTO :en;"
EXEC SQL FETCH C14721 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14721 INTO :en;"
EXEC SQL FETCH C14721 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14721;"
EXEC SQL CLOSE C14721 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14722 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM"
DISPLAY " (HU.STAFF3 NATURAL LEFT JOIN HU.STAFF)"
DISPLAY " NATURAL INNER JOIN HU.STAFF4"
DISPLAY " ORDER BY EMPNUM ASC;"
EXEC SQL DECLARE C14722 CURSOR FOR
SELECT EMPNUM FROM
(HU.STAFF3 NATURAL LEFT JOIN HU.STAFF)
NATURAL INNER JOIN HU.STAFF4
ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14722;"
EXEC SQL OPEN C14722 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14722 INTO :en;"
EXEC SQL FETCH C14722 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14722 INTO :en;"
EXEC SQL FETCH C14722 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14722 INTO :en;"
EXEC SQL FETCH C14722 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14722;"
EXEC SQL CLOSE C14722 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14723 CURSOR FOR"
DISPLAY " SELECT EMPNUM FROM"
DISPLAY " HU.STAFF3 NATURAL LEFT JOIN"
DISPLAY " (HU.STAFF NATURAL INNER JOIN HU.STAFF4)"
DISPLAY " ORDER BY EMPNUM;"
EXEC SQL DECLARE C14723 CURSOR FOR
SELECT EMPNUM FROM
HU.STAFF3 NATURAL LEFT JOIN
(HU.STAFF NATURAL INNER JOIN HU.STAFF4)
ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14723;"
EXEC SQL OPEN C14723 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E1 '; its value is '", en, "'"
if (en NOT = "E1 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
if (en NOT = "E2 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
if (en NOT = "E3 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
if (en NOT = "E4 ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14723 INTO :en;"
EXEC SQL FETCH C14723 INTO :en END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14723;"
EXEC SQL CLOSE C14723 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
if ( flag = 1 ) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0841','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.pco *** fail *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0841','fail','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0841 ********************
******************** BEGIN TEST0842 *******************
MOVE 1 TO flag
DISPLAY " TEST0842 "
DISPLAY " Multi-column joins"
DISPLAY "References:"
DISPLAY " F# 4 -- Joined table"
DISPLAY " - - - - - - - - - - - - - - - - - - -"
COMPUTE SQLCODE = -1
MOVE "xxxxx" TO SQLSTATE
DISPLAY "CREATE TABLE STAFF66 ("
DISPLAY " SALARY INTEGER,"
DISPLAY " EMPNAME CHAR(20),"
DISPLAY " GRADE DECIMAL,"
DISPLAY " EMPNUM CHAR(3));"
EXEC SQL CREATE TABLE STAFF66 (
SALARY INTEGER,
EMPNAME CHAR(20),
GRADE DECIMAL,
EMPNUM CHAR(3)) END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "INSERT INTO STAFF66"
DISPLAY " SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM"
DISPLAY " FROM HU.STAFF3 WHERE EMPNUM > 'E2';"
EXEC SQL INSERT INTO STAFF66
SELECT GRADE*1000, EMPNAME, GRADE, EMPNUM
FROM HU.STAFF3 WHERE EMPNUM > 'E2' END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM =
- " 'E5';"
EXEC SQL UPDATE HU.STAFF3 SET EMPNUM = 'E6' WHERE EMPNUM =
'E5' END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE =
- " 12;"
EXEC SQL UPDATE HU.STAFF3 SET EMPNAME = 'Ali' WHERE GRADE =
12 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*7.5 SR 6 d
*Table STAFF66 has 3 rows, only 1 matching on all columns
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE 0 TO sl
MOVE "xxxxxxxxxxxxxxx" TO ct
DISPLAY "SELECT * INTO :ename, :gr, :en, :sl, :ct FROM"
DISPLAY " STAFF66 NATURAL INNER JOIN HU.STAFF3;"
EXEC SQL SELECT * INTO :ename, :gr, :en, :sl, :ct FROM
STAFF66 NATURAL INNER JOIN HU.STAFF3 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (gr NOT = 13 OR sl NOT = 13000) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR en NOT =
"E3 ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
DISPLAY "DECLARE C14731 CURSOR FOR"
DISPLAY " SELECT EMPNUM, EMPNAME, SALARY FROM"
DISPLAY " HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM ASC;"
EXEC SQL DECLARE C14731 CURSOR FOR
SELECT EMPNUM, EMPNAME, SALARY FROM
HU.STAFF3 NATURAL LEFT OUTER JOIN STAFF66
WHERE EMPNUM > 'E1'
ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14731;"
EXEC SQL OPEN C14731 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ename should be 'Ali '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E4 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ali ") then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E6 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14731 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14731 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14731;"
EXEC SQL CLOSE C14731 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14732 CURSOR FOR"
DISPLAY " SELECT EMPNUM, EMPNAME, SALARY FROM"
DISPLAY " STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM DESC;"
EXEC SQL DECLARE C14732 CURSOR FOR
SELECT EMPNUM, EMPNAME, SALARY FROM
STAFF66 NATURAL RIGHT OUTER JOIN HU.STAFF
WHERE EMPNUM > 'E1'
ORDER BY EMPNUM DESC END-EXEC
DISPLAY "OPEN C14732;"
EXEC SQL OPEN C14732 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E5 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ename should be 'Don '; its value
- " is '", ename, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E4 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Don " OR sl NOT =
12000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
COMPUTE indic1 = -1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR sl NOT =
13000) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO indic1
MOVE 0 TO sl
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14732 INTO :en, :ename, :sl:indic1;"
EXEC SQL FETCH C14732 INTO :en, :ename, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14732;"
EXEC SQL CLOSE C14732 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14733 CURSOR FOR"
DISPLAY " SELECT * FROM STAFF66 RIGHT JOIN HU.STAFF"
DISPLAY " USING (GRADE, EMPNUM, EMPNAME)"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM;"
EXEC SQL DECLARE C14733 CURSOR FOR
SELECT * FROM STAFF66 RIGHT JOIN HU.STAFF
USING (GRADE, EMPNUM, EMPNAME)
WHERE EMPNUM > 'E1'
ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14733;"
EXEC SQL OPEN C14733 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
:ct END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "gr should be 10; its value is ", gr
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (en NOT = "E2 " OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty " OR gr NOT =
10) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
:ct END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
if (en NOT = "E3 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen " OR gr NOT =
13) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna " OR sl NOT = 13000)
then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
:ct END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Don '; its value
- " is '", ename, "'"
DISPLAY "gr should be 12; its value is ", gr
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
if (en NOT = "E4 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Don " OR gr NOT =
12) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Deale " OR sl NOT = 12000)
then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
:ct END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
if (en NOT = "E5 " OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed " OR gr NOT =
13) then
MOVE 0 TO flag
END-IF
if (ct NOT = "Akron " OR sl NOT = 13000)
then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
- " :ct;"
EXEC SQL FETCH C14733 INTO :ename, :gr, :en, :sl:indic1,
:ct END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14733;"
EXEC SQL CLOSE C14733 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DECLARE C14734 CURSOR FOR"
DISPLAY " SELECT * FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM > 'E1'"
DISPLAY " ORDER BY EMPNUM ASC;"
EXEC SQL DECLARE C14734 CURSOR FOR
SELECT * FROM HU.STAFF3 LEFT JOIN STAFF66
USING (GRADE, EMPNUM)
WHERE EMPNUM > 'E1'
ORDER BY EMPNUM ASC END-EXEC
DISPLAY "OPEN C14734;"
EXEC SQL OPEN C14734 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
:sl:indic1, :ename2:indic2 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "gr should be 10; its value is ", gr
DISPLAY "ename should be 'Betty '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "indic2 should be -1; its value is ", indic2
if (en NOT = "E2 " OR gr NOT = 10) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Betty ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1 OR indic2 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
:sl:indic1, :ename2:indic2 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic2 should be 0; its value is ", indic2
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = 0 OR indic2 NOT = 0) then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
:sl:indic1, :ename2:indic2 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "gr should be 12; its value is ", gr
DISPLAY "ename should be 'Ali '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "indic1 should be 0; its value is ", indic1
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic2 should be 0; its value is ", indic2
DISPLAY "ename2 should be 'Don '; its value
- " is '", ename2, "'"
if (en NOT = "E4 " OR gr NOT = 12) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ali ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Deale ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = 0 OR indic2 NOT = 0) then
MOVE 0 TO flag
END-IF
if (sl NOT = 12000 OR ename2 NOT = "Don
- " ") then
MOVE 0 TO flag
END-IF
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 2 TO indic1
MOVE 2 TO indic2
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
:sl:indic1, :ename2:indic2 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Ed '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
DISPLAY "indic2 should be -1; its value is ", indic2
if (en NOT = "E6 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Ed ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Akron ") then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1 OR indic2 NOT = -1) then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14734 INTO :en, :gr, :ename, :ct,"
DISPLAY " :sl:indic1, :ename2:indic2;"
EXEC SQL FETCH C14734 INTO :en, :gr, :ename, :ct,
:sl:indic1, :ename2:indic2 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "CLOSE C14734;"
EXEC SQL CLOSE C14734 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*Similar to above, except for explicit names of columns
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,"
DISPLAY " SALARY, STAFF66.EMPNAME"
DISPLAY " INTO :en, :gr, :ename, :ct, :sl, :ename2"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM = 'E3';"
EXEC SQL SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,
SALARY, STAFF66.EMPNAME
INTO :en, :gr, :ename, :ct, :sl, :ename2
FROM HU.STAFF3 LEFT JOIN STAFF66
USING (GRADE, EMPNUM)
WHERE EMPNUM = 'E3' END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
DISPLAY "DECLARE C14735 CURSOR FOR"
DISPLAY " SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,"
DISPLAY " SALARY, STAFF66.EMPNAME"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66"
DISPLAY " USING (GRADE, EMPNUM)"
DISPLAY " WHERE EMPNUM = 'E3';"
EXEC SQL DECLARE C14735 CURSOR FOR
SELECT EMPNUM, GRADE, HU.STAFF3.EMPNAME, CITY,
SALARY, STAFF66.EMPNAME
FROM HU.STAFF3 LEFT JOIN STAFF66
USING (GRADE, EMPNUM)
WHERE EMPNUM = 'E3' END-EXEC
DISPLAY "OPEN C14735;"
EXEC SQL OPEN C14735 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename
MOVE "xxxxxxxxxxxxxxxxxxxx" TO ename2
MOVE 0 TO gr
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
DISPLAY "FETCH C14735 INTO :en, :gr, :ename, :ct, :sl,
- " :ename2;"
EXEC SQL FETCH C14735 INTO :en, :gr, :ename, :ct, :sl,
:ename2 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "gr should be 13; its value is ", gr
DISPLAY "ename should be 'Carmen '; its value
- " is '", ename, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "ename2 should be 'Carmen '; its value
- " is '", ename2, "'"
if (en NOT = "E3 " OR gr NOT = 13) then
MOVE 0 TO flag
END-IF
if (ename NOT = "Carmen ") then
MOVE 0 TO flag
END-IF
if (ct NOT = "Vienna ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR ename2 NOT = "Carmen
- " ") then
MOVE 0 TO flag
END-IF
DISPLAY "CLOSE C14735;"
EXEC SQL CLOSE C14735 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
*REF: 7.5 GR 1 d ii This is a cartesian product
MOVE 0 TO int1
DISPLAY "SELECT COUNT (*) INTO :int1"
DISPLAY " FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ;"
EXEC SQL SELECT COUNT (*) INTO :int1
FROM STAFF66 NATURAL RIGHT JOIN HU.PROJ END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "int1 should be 18; its value is ", int1
if (int1 NOT = 18) then
MOVE 0 TO flag
END-IF
*FULL OUTER JOIN of tables with unique data in the joined column
DISPLAY "DECLARE C14736 CURSOR FOR"
DISPLAY " SELECT EMPNUM, CITY, SALARY"
DISPLAY " FROM HU.STAFF3 LEFT JOIN STAFF66 USING
- " (EMPNUM)"
DISPLAY " UNION"
DISPLAY " SELECT EMPNUM, CITY, SALARY"
DISPLAY " FROM HU.STAFF3 RIGHT JOIN STAFF66 USING
- " (EMPNUM)"
DISPLAY " ORDER BY EMPNUM;"
EXEC SQL DECLARE C14736 CURSOR FOR
SELECT EMPNUM, CITY, SALARY
FROM HU.STAFF3 LEFT JOIN STAFF66 USING (EMPNUM)
UNION
SELECT EMPNUM, CITY, SALARY
FROM HU.STAFF3 RIGHT JOIN STAFF66 USING (EMPNUM)
ORDER BY EMPNUM END-EXEC
DISPLAY "OPEN C14736;"
EXEC SQL OPEN C14736 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E1 '; its value is '", en, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E1 " OR ct NOT = "Deale ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E2 '; its value is '", en, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E2 " OR ct NOT = "Vienna ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
COMPUTE indic1 = -1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E3 '; its value is '", en, "'"
DISPLAY "ct should be 'Vienna '; its value is '",
ct, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E3 " OR ct NOT = "Vienna ")
then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
COMPUTE indic1 = -1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E4 '; its value is '", en, "'"
DISPLAY "ct should be 'Deale '; its value is '",
ct, "'"
DISPLAY "sl should be 12000; its value is ", sl
DISPLAY "indic1 should be 0; its value is ", indic1
if (en NOT = "E4 " OR ct NOT = "Deale ")
then
MOVE 0 TO flag
END-IF
if (sl NOT = 12000 OR indic1 NOT = 0) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO sl
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct:indic1, :sl;"
EXEC SQL FETCH C14736 INTO :en, :ct:indic1, :sl END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E5 '; its value is '", en, "'"
DISPLAY "sl should be 13000; its value is ", sl
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E5 ") then
MOVE 0 TO flag
END-IF
if (sl NOT = 13000 OR indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
MOVE "xxx" TO en
MOVE "xxxxxxxxxxxxxxx" TO ct
MOVE 0 TO indic1
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY "en should be 'E6 '; its value is '", en, "'"
DISPLAY "ct should be 'Akron '; its value is '",
ct, "'"
DISPLAY "indic1 should be -1; its value is ", indic1
if (en NOT = "E6 " OR ct NOT = "Akron ")
then
MOVE 0 TO flag
END-IF
if (indic1 NOT = -1) then
MOVE 0 TO flag
END-IF
DISPLAY "FETCH C14736 INTO :en, :ct, :sl:indic1;"
EXEC SQL FETCH C14736 INTO :en, :ct, :sl:indic1 END-EXEC
MOVE SQLCODE TO SQL-COD
DISPLAY "SQLCODE should be 100; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 02000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 100 OR NORMSQ NOT = "02000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "02000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
DISPLAY " "
DISPLAY "ROLLBACK WORK;"
EXEC SQL ROLLBACK WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "DROP TABLE STAFF66 CASCADE;"
EXEC SQL DROP TABLE STAFF66 CASCADE END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
DISPLAY "COMMIT WORK;"
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
PERFORM CHCKOK
DISPLAY " "
if ( flag = 1 ) then
DISPLAY " *** pass *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0842','pass','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
else
DISPLAY " dml147.pco *** fail *** "
EXEC SQL INSERT INTO HU.TESTREPORT
VALUES('0842','fail','PCO') END-EXEC
MOVE SQLCODE TO SQL-COD
COMPUTE errcnt = errcnt + 1
END-IF
DISPLAY "==============================================="
EXEC SQL COMMIT WORK END-EXEC
MOVE SQLCODE TO SQL-COD
******************** END TEST0842 ********************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0
STOP RUN.
* **** Procedures for PERFORM statements
*Test SQLCODE and SQLSTATE for normal completion.
CHCKOK.
DISPLAY "SQLCODE should be 0; its value is ", SQL-COD
DISPLAY "SQLSTATE should be 00000; its value is ", SQLSTATE
PERFORM NOSUBCLASS THRU EXIT-NOSUBCLASS
if (SQLCODE NOT = 0 OR NORMSQ NOT = "00000") then
MOVE 0 TO flag
END-IF
if (NORMSQ = "00000" AND NORMSQ NOT = SQLSTATE)
then
DISPLAY "Valid implementation-defined SQLSTATE accepted."
END-IF
.
NOSUBCLASS.
*This routine replaces valid implementation-defined
*subclasses with 000. This replacement equates valid
*implementation-defined subclasses with the 000 value
*expected by the test case; otherwise the test will fail.
*After calling NOSUBCLASS, NORMSQ will be tested
* SQLSTATE will be printed.
MOVE SQLSTATE TO NORMSQ
MOVE 3 TO norm1
*subclass begins in position 3 of char array NORMSQ
*valid subclass begins with 5-9, I-Z, end of ALPNUM table
PERFORM VARYING norm2 FROM 14 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
*Quit if NORMSQ is unchanged. Subclass is not impl.-def.
*Changed NORMSQ means implementation-defined subclass,
*so proceed to zero it out, if valid (0-9,A-Z)
if (NORMSQ = SQLSTATE) then
GO TO EXIT-NOSUBCLASS
END-IF
MOVE 4 TO norm1
*examining position 4 of char array NORMSQ
*valid characters are 0-9, A-Z
PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
MOVE 5 TO norm1
*valid characters are 0-9, A-Z
*examining position 5 of char array NORMSQ
PERFORM VARYING norm2 FROM 1 BY 1 UNTIL norm2 > 36
if (NORMSQX(norm1) = ALPNUM(norm2)) then
MOVE "0" TO NORMSQX(norm1)
END-IF
END-PERFORM
*implementation-defined subclasses are allowed for warnings
*(class = 01). These equate to successful completion
*SQLSTATE values of 00000.
*Reference SQL-92 4.28 SQL-transactions, paragraph 2
if (NORMSQX(1) = "0" AND NORMSQX(2) = "1") then
MOVE "0" TO NORMSQX(2)
END-IF
.
EXIT-NOSUBCLASS.
EXIT.
¤ Dauer der Verarbeitung: 0.49 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.
|
