| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML069 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml069.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 |
| |
| -- NO_TEST:0404 2 FETCHes (different target types) on same cursor! |
| |
| -- Testing cursors |
| |
| -- *************************************************************** |
| |
| -- NO_TEST:0405 2 cursors open from different schemas (coded join)! |
| |
| -- Testing cursors |
| |
| -- *************************************************************** |
| |
| -- TEST:0406 Subquery from different schema! |
| |
| --O DELETE FROM CUGINI.VTABLE; |
| DELETE FROM VTABLE; |
| 4 rows inserted/updated/deleted |
| ij> -- Making sure the table is empty |
| |
| -- setup |
| --O INSERT INTO CUGINI.VTABLE VALUES (80, 100, 100, 100, 100.0); |
| INSERT INTO VTABLE VALUES (80, 100, 100, 100, 100.0); |
| 1 row inserted/updated/deleted |
| ij> --O INSERT INTO CUGINI.VTABLE VALUES (40, 200, 100, 100, 100.0); |
| INSERT INTO VTABLE VALUES (40, 200, 100, 100, 100.0); |
| 1 row inserted/updated/deleted |
| ij> SELECT PNUM |
| FROM WORKS |
| WHERE EMPNUM = 'E1' AND HOURS IN |
| --O (SELECT COL1 FROM CUGINI.VTABLE |
| (SELECT COL1 FROM VTABLE |
| WHERE COL1 > 50); |
| PN& |
| --- |
| P3 |
| ij> -- PASS:0406 If PNUM = 'P3'? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0406 <<< END TEST |
| -- ************************************************************* |
| |
| -- NO_TEST:0407 SELECT INTO :XX ... WHERE :XX = ! |
| |
| -- Testing host variables |
| |
| -- ************************************************************** |
| |
| -- TEST:0408 UPDATE references column value BEFORE update! |
| |
| 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 SELECT * FROM WORKS; |
| 12 rows inserted/updated/deleted |
| ij> UPDATE WORKS1 |
| SET PNUM = EMPNUM, EMPNUM = PNUM, HOURS = (HOURS + 3) * HOURS; |
| 12 rows inserted/updated/deleted |
| ij> SELECT * |
| FROM WORKS1 |
| WHERE EMPNUM = 'P2' |
| ORDER BY EMPNUM, PNUM ASC; |
| EM&|PN&|HOURS |
| -------------- |
| P2 |E1 |460 |
| P2 |E2 |6640 |
| P2 |E3 |460 |
| P2 |E4 |460 |
| ij> -- PASS:0408 If FOR ROW #1, EMPNO1 = 'P2', PNUM1 = 'E1', HOURS1 = 460? |
| -- PASS:0408 If FOR ROW #2, EMPNO1 = 'P2', PNUM1 = 'E2',HOURS1 = 6640? |
| -- PASS:0408 If FOR ROW #3, EMPNO1 = 'P2', PNUM1 = 'E3', HOURS1 = 460? |
| -- PASS:0408 If FOR ROW #4, EMPNO1 = 'P2', PNUM1 = 'E4', HOURS1 = 460? |
| |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0408 <<< END TEST |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |