ij> AUTOCOMMIT OFF; ij> -- MODULE DML060 -- SQL Test Suite, V6.0, Interactive SQL, dml060.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:0261 WHERE (2 * (c1 - c2)) BETWEEN! SELECT COL1, COL2 FROM VTABLE WHERE(2*(COL3 - COL2)) BETWEEN 5 AND 200 ORDER BY COL1; COL1 |COL2 ----------------------- 10 |20 100 |200 ij> -- PASS:0261 If 2 rows are selected ? -- PASS:0261 If first row is ( 10, 20)? -- PASS:0261 If second row is (100, 200)? -- END TEST >>> 0261 <<< END TEST -- ******************************************************************** -- TEST:0262 WHERE clause with computation, ANY/ALL subqueries! UPDATE VTABLE SET COL1 = 1 WHERE COL1 = 0; 1 row inserted/updated/deleted ij> -- PASS:0262 If 1 row is updated? SELECT COL1, COL2 FROM VTABLE WHERE (COL3 * COL2/COL1) > ALL (SELECT HOURS FROM WORKS) OR -(COL3 * COL2/COL1) > ANY (SELECT HOURS FROM WORKS) ORDER BY COL1; COL1 |COL2 ----------------------- 100 |200 1000 |-2000 ij> -- PASS:0262 If 2 rows are selected? -- PASS:0262 If first row is ( 100, 200)? -- PASS:0262 If second row is (1000, -2000)? -- restore ROLLBACK WORK; ij> -- END TEST >>> 0262 <<< END TEST -- ****************************************************************** -- TEST:0263 Computed column in ORDER BY! SELECT COL1, (COL3 * COL2/COL1 - COL2 + 10) FROM VTABLE WHERE COL1 > 0 ORDER BY 2; COL1 |2 ----------------------- 1000 |-3990 10 |50 100 |410 ij> -- PASS:0263 If 3 rows are selected in order with values:? -- PASS:0263 (1000, -3990)? -- PASS:0263 ( 10, 50)? -- PASS:0263 ( 100, 410)? -- END TEST >>> 0263 <<< END TEST -- ******************************************************************** -- TEST:0265 Update:searched - view with check option! -- setup INSERT INTO WORKS VALUES('E3','P4',50); 1 row inserted/updated/deleted ij> -- PASS:0265 If 1 row is inserted? SELECT EMPNUM, PNUM, HOURS FROM SUBSP; EM&|PN&|HOURS -------------- E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0265 If 2 rows are selected? SELECT * FROM WORKS; EM&|PN&|HOURS -------------- E1 |P1 |40 E1 |P2 |20 E1 |P3 |80 E1 |P4 |20 E1 |P5 |12 E1 |P6 |12 E2 |P1 |40 E2 |P2 |80 E3 |P2 |20 E4 |P2 |20 E4 |P4 |40 E4 |P5 |80 E3 |P4 |50 ij> -- PASS:0265 If 13 rows selected? --O UPDATE SUBSP --O SET EMPNUM = 'E9' --O WHERE PNUM = 'P2'; -- PASS:0265 If ERROR, view check constraint, 0 rows are updated? SELECT * FROM WORKS; EM&|PN&|HOURS -------------- E1 |P1 |40 E1 |P2 |20 E1 |P3 |80 E1 |P4 |20 E1 |P5 |12 E1 |P6 |12 E2 |P1 |40 E2 |P2 |80 E3 |P2 |20 E4 |P2 |20 E4 |P4 |40 E4 |P5 |80 E3 |P4 |50 ij> -- PASS:0265 If 13 rows selected and no EMPNUM = 'E9'? -- restore ROLLBACK WORK; ij> -- END TEST >>> 0265 <<< END TEST -- ****************************************************************** -- TEST:0266 Update:searched - UNIQUE violation under view! -- setup INSERT INTO WORKS VALUES('E3','P4',50); 1 row inserted/updated/deleted ij> -- PASS:0266 If 1 row is inserted? SELECT EMPNUM, PNUM, HOURS FROM SUBSP; EM&|PN&|HOURS -------------- E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0266 If 2 rows are selected? SELECT * FROM WORKS WHERE EMPNUM = 'E3'; EM&|PN&|HOURS -------------- E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'? --O UPDATE SUBSP --O SET PNUM = 'P6' --O WHERE EMPNUM = 'E3'; -- PASS:0266 If ERROR, unique constraint, 0 rows updated? --O SELECT EMPNUM, PNUM, HOURS --O FROM SUBSP; -- PASS:0266 If 2 rows are selected? --O SELECT * FROM WORKS WHERE EMPNUM = 'E3'; -- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'? -- restore ROLLBACK WORK; ij> -- END TEST >>> 0266 <<< END TEST -- ****************************************************************** -- TEST:0267 Update compound key, interim uniqueness conflict! DELETE FROM WORKS1; 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> -- Making sure the table is empty -- setup INSERT INTO WORKS1 VALUES ('P1','P6',1); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P6',2); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P6',3); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P6',4); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P6',5); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P6',6); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P1','P5',7); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P5',8); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P5',9); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P5',10); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P5',11); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P5',12); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P1','P4',13); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P4',14); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P4',15); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P4',16); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P4',17); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P4',18); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P1','P3',19); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P3',20); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P3',21); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P3',22); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P3',23); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P3',24); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P1','P2',25); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P2',26); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P2',27); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P2',28); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P2',29); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P2',30); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P1','P1',31); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P2','P1',32); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P3','P1',33); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P4','P1',34); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P5','P1',35); 1 row inserted/updated/deleted ij> INSERT INTO WORKS1 VALUES ('P6','P1',36); 1 row inserted/updated/deleted ij> UPDATE WORKS1 SET PNUM = EMPNUM, EMPNUM = PNUM; 36 rows inserted/updated/deleted ij> -- PASS:0267 If 36 rows are updated? --O SELECT COUNT(*) SELECT * FROM WORKS1 WHERE EMPNUM = 'P1' AND HOURS > 30; EM&|PN&|HOURS -------------- P1 |P1 |31 P1 |P2 |32 P1 |P3 |33 P1 |P4 |34 P1 |P5 |35 P1 |P6 |36 ij> -- PASS:0267 If count = 6? -- restore ROLLBACK WORK; ij> -- END TEST >>> 0267 <<< END TEST -- *************************************************////END-OF-MODULE ; ij>