dml069.out

derby database source code.good for you.

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