ij> AUTOCOMMIT OFF;
ij> -- MODULE DML009

-- SQL Test Suite, V6.0, Interactive SQL, dml009.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION HU
   set schema HU;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
HU                                                                                                                              
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment

-- date_time print

-- TEST:0022 INSERT(column list) VALUES(literals and NULL)!

-- setup
     INSERT INTO WORKS(PNUM,EMPNUM,HOURS)
            VALUES ('P22','E22',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0022 If 1 row inserted?

      SELECT EMPNUM,PNUM
           FROM   WORKS
           WHERE  HOURS IS NULL;
EM&|PN&
-------
E22|P22
ij> -- PASS:0022 If EMPNUM = 'E22'?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0022 <<< END TEST
-- **************************************************************

-- TEST:0023 DEC precision >= col.def.: ERROR if left-truncate!

-- setup
     DELETE FROM TEMP_S;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> COMMIT WORK;
ij> -- setup
     INSERT INTO TEMP_S(EMPNUM,GRADE,CITY)
          VALUES('E23',2323.4,'China');
1 row inserted/updated/deleted
ij> -- PASS:0023 If 1 row inserted?

--O     SELECT COUNT(*)
     SELECT *
          FROM TEMP_S;
EMP&|GRADE|CITY           
--------------------------
E23 |2323 |China          
ij> -- PASS:0023 If count = 1?

-- setup
     INSERT INTO TEMP_S
            VALUES('E23',23234,'China');
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(4,0).
ij> -- PASS:0023 If 1 row inserted or ?
-- PASS:0023 insert fails due to precision of 23234?

--O      SELECT COUNT(*)
      SELECT *
           FROM TEMP_S;
EMP&|GRADE|CITY           
--------------------------
E23 |2323 |China          
ij> -- PASS:0023 If count = 1 or 2 (depending on previous insertion)?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0023 <<< END TEST
-- ***************************************************************

-- TEST:0024 INSERT:<query spec.> is empty: SQLCODE = 100!

-- setup
     INSERT INTO TEMP_S
            SELECT EMPNUM,GRADE,CITY
                 FROM STAFF
                 WHERE GRADE > 13;
0 rows inserted/updated/deleted
ij> -- PASS:0024 If 0 rows selected, SQLCODE = 100, end of data?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0024 <<< END TEST
-- *************************************************************

-- TEST:0025 INSERT:<query spec.> is not empty!
     DELETE FROM TEMP_S;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> -- setup
     INSERT INTO TEMP_S(EMPNUM,GRADE,CITY)
            SELECT EMPNUM,GRADE,CITY
                 FROM STAFF
                 WHERE GRADE > 12;
2 rows inserted/updated/deleted
ij> -- PASS:0025 If 2 rows are inserted?

--O      SELECT COUNT(*)
      SELECT *
           FROM TEMP_S;
EMP&|GRADE|CITY           
--------------------------
E3  |13   |Vienna         
E5  |13   |Akron          
ij> -- PASS:0025 If count = 2?

-- restore
     ROLLBACK WORK;
ij> -- END TEST >>> 0025 <<< END TEST
-- *************************************************************

-- TEST:0026 INSERT into view with check option and unique violation!

--0     SELECT COUNT(*) FROM STAFF;
-- PASS:0026 If count = 5?

-- setup
--0     INSERT INTO TEMP_SS
--0            SELECT EMPNUM,GRADE,CITY
--0                 FROM STAFF3
--0                 WHERE GRADE = 10;
-- PASS:0026 If ERROR, view check constraint, 0 rows inserted  OR ?
-- PASS:0026 If ERROR, unique constraint, 0 rows inserted?

--0     SELECT COUNT(*) FROM STAFF;
-- PASS:0026 If count = 5?

-- restore
--0     ROLLBACK WORK;

-- END TEST >>> 0026 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>