Quelle  dml147.cob   Sprache: Cobol

       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.