| products/Sources/formale Sprachen/Cobol/Test-Suite/SQL M/ |
|
Quellcode-Bibliothek© Kompilation durch diese Firma[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]Datei: predml.cob Sprache: Cobol |
|
||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML147. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML147.SCO") calling SQL * procedures in file "DML147.MCO". **************************************************************** * * COMMENT SECTION * * DATE 1995/2/1 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. * * DML147.SCO * WRITTEN BY: Joan Sullivan (mostly) * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * Standard 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; CALL "SUB1" USING SQLCODE SQLSTATE uidx MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB2" USING SQLCODE SQLSTATE 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, Module COBOL, dml147.sco" 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)); CALL "SUB3" USING SQLCODE SQLSTATE 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; CALL "SUB4" USING SQLCODE SQLSTATE 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') * ; CALL "SUB5" USING SQLCODE SQLSTATE 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; CALL "SUB6" USING SQLCODE SQLSTATE 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; CALL "SUB7" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB8" USING SQLCODE SQLSTATE 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; CALL "SUB9" USING SQLCODE SQLSTATE int1 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; CALL "SUB10" USING SQLCODE SQLSTATE 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; CALL "SUB11" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB12" USING SQLCODE SQLSTATE 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; CALL "SUB13" USING SQLCODE SQLSTATE int1 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; CALL "SUB14" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE USIG CASCADE;" * EXEC SQL DROP TABLE USIG CASCADE; CALL "SUB15" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB16" USING SQLCODE SQLSTATE 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; CALL "SUB17" USING SQLCODE SQLSTATE int1 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; CALL "SUB18" USING SQLCODE SQLSTATE 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; CALL "SUB19" USING SQLCODE SQLSTATE int1 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; CALL "SUB20" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0840','pass','MCO'); CALL "SUB21" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml147.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0840','fail','MCO'); CALL "SUB22" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB23" USING SQLCODE SQLSTATE 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 DISPLAY " - - - - - - - - - - - - - - - - - - -" COMPUTE SQLCODE = -1 MOVE "xxxxx" TO SQLSTATE DISPLAY "DELETE FROM HU.STAFF4;" * EXEC SQL DELETE FROM HU.STAFF4; CALL "SUB24" USING SQLCODE SQLSTATE 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'; CALL "SUB25" USING SQLCODE SQLSTATE 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; CALL "SUB26" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO en DISPLAY "FETCH C14721 INTO :en;" * EXEC SQL FETCH C14721 INTO :en; CALL "SUB27" USING SQLCODE SQLSTATE en 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; CALL "SUB28" USING SQLCODE SQLSTATE en 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; CALL "SUB29" USING SQLCODE SQLSTATE en 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; CALL "SUB30" USING SQLCODE SQLSTATE 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; CALL "SUB31" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO en DISPLAY "FETCH C14722 INTO :en;" * EXEC SQL FETCH C14722 INTO :en; CALL "SUB32" USING SQLCODE SQLSTATE en 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; CALL "SUB33" USING SQLCODE SQLSTATE en 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; CALL "SUB34" USING SQLCODE SQLSTATE en 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; CALL "SUB35" USING SQLCODE SQLSTATE 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; CALL "SUB36" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " MOVE "xxx" TO en DISPLAY "FETCH C14723 INTO :en;" * EXEC SQL FETCH C14723 INTO :en; CALL "SUB37" USING SQLCODE SQLSTATE en 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; CALL "SUB38" USING SQLCODE SQLSTATE en 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; CALL "SUB39" USING SQLCODE SQLSTATE en 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; CALL "SUB40" USING SQLCODE SQLSTATE en 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; CALL "SUB41" USING SQLCODE SQLSTATE en 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; CALL "SUB42" USING SQLCODE SQLSTATE en 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; CALL "SUB43" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "ROLLBACK WORK;" * EXEC SQL ROLLBACK WORK; CALL "SUB44" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0841','pass','MCO'); CALL "SUB45" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml147.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0841','fail','MCO'); CALL "SUB46" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB47" USING SQLCODE SQLSTATE 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)); CALL "SUB48" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB49" USING SQLCODE SQLSTATE 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'; CALL "SUB50" USING SQLCODE SQLSTATE 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'; CALL "SUB51" USING SQLCODE SQLSTATE 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; CALL "SUB52" USING SQLCODE SQLSTATE 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; CALL "SUB53" USING SQLCODE SQLSTATE ename gr en sl ct 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; CALL "SUB54" USING SQLCODE SQLSTATE 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; CALL "SUB55" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB56" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB57" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB58" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB59" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB60" USING SQLCODE SQLSTATE 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; CALL "SUB61" USING SQLCODE SQLSTATE 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; CALL "SUB62" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB63" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB64" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB65" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB66" USING SQLCODE SQLSTATE en ename sl indic1 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; CALL "SUB67" USING SQLCODE SQLSTATE 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; CALL "SUB68" USING SQLCODE SQLSTATE 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; CALL "SUB69" USING SQLCODE SQLSTATE ename gr en sl indic1 ct 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; CALL "SUB70" USING SQLCODE SQLSTATE ename gr en sl indic1 ct 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; CALL "SUB71" USING SQLCODE SQLSTATE ename gr en sl indic1 ct 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; CALL "SUB72" USING SQLCODE SQLSTATE ename gr en sl indic1 ct 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; CALL "SUB73" USING SQLCODE SQLSTATE ename gr en sl indic1 ct 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; CALL "SUB74" USING SQLCODE SQLSTATE 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; CALL "SUB75" USING SQLCODE SQLSTATE 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; CALL "SUB76" USING SQLCODE SQLSTATE en gr ename ct sl indic1 ename2 indic2 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; CALL "SUB77" USING SQLCODE SQLSTATE en gr ename ct sl indic1 ename2 indic2 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; CALL "SUB78" USING SQLCODE SQLSTATE en gr ename ct sl indic1 ename2 indic2 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; CALL "SUB79" USING SQLCODE SQLSTATE en gr ename ct sl indic1 ename2 indic2 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; CALL "SUB80" USING SQLCODE SQLSTATE en gr ename ct sl indic1 ename2 indic2 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; CALL "SUB81" USING SQLCODE SQLSTATE 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'; CALL "SUB82" USING SQLCODE SQLSTATE en gr ename ct sl ename2 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; CALL "SUB83" USING SQLCODE SQLSTATE 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; CALL "SUB84" USING SQLCODE SQLSTATE en gr ename ct sl ename2 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; CALL "SUB85" USING SQLCODE SQLSTATE 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; CALL "SUB86" USING SQLCODE SQLSTATE int1 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; CALL "SUB87" USING SQLCODE SQLSTATE 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; CALL "SUB88" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB89" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB90" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB91" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB92" USING SQLCODE SQLSTATE en ct indic1 sl 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; CALL "SUB93" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB94" USING SQLCODE SQLSTATE en ct sl indic1 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; CALL "SUB95" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "DROP TABLE STAFF66 CASCADE;" * EXEC SQL DROP TABLE STAFF66 CASCADE; CALL "SUB96" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " DISPLAY "COMMIT WORK;" * EXEC SQL COMMIT WORK; CALL "SUB97" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD PERFORM CHCKOK DISPLAY " " if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0842','pass','MCO'); CALL "SUB98" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD else DISPLAY " dml147.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0842','fail','MCO'); CALL "SUB99" USING SQLCODE SQLSTATE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF DISPLAY "===============================================" --> -------------------- --> maximum size reached --> -------------------- ¤ Dauer der Verarbeitung: 0.113 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.Bot Zugriff |
