dml009.out

derby database source code.good for you.

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
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
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>