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>