dml144.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML144  
-- SQL Test Suite, V6.0, Interactive SQL, dml144.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O   ROLLBACK WORK;
-- date_time print
-- TEST:0834 <length expression> (static)!
   CREATE TABLE GRUB (C1 VARCHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0834 If table is created?
   COMMIT WORK;
ij> SELECT LENGTH (EMPNAME)
   FROM HU.STAFF WHERE GRADE = 10;
1          
-----------
20         
ij> -- PASS:0834 If 1 row selected and value is 20?
   SELECT LENGTH ('HI' || 'THERE')
   FROM HU.ECCO;
1          
-----------
7          
ij> -- PASS:0834 If 1 row selected and value is 7?
   INSERT INTO GRUB VALUES ('Hi  ');
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is inserted?
   SELECT LENGTH (C1)
   FROM GRUB;
1          
-----------
4          
ij> -- PASS:0834 If 1 row selected and value is 4?
-- following is not supported in derby
--   SELECT OCTET_LENGTH (C1)
--  FROM GRUB;
-- PASS:0834 If 1 row selected and value is > 2?
   UPDATE GRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is updated?
   SELECT LENGTH (C1)
  FROM GRUB;
1          
-----------
NULL       
ij> -- PASS:0834 If 1 row selected and value is NULL?
-- following is not supported in derby
--   SELECT OCTET_LENGTH (C1)
--  FROM GRUB;
-- PASS:0834 If 1 row selected and value is NULL?
   ROLLBACK WORK;
ij> --O   DROP TABLE GRUB CASCADE;
   DROP TABLE GRUB ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0834 <<< END TEST
-- *********************************************
-- TEST:0835 <character substring function> (static)!
   CREATE TABLE MOREGRUB (C1 VARCHAR (10), ID INT);
0 rows inserted/updated/deleted
ij> -- PASS:0835 If table is created?
   COMMIT WORK;
ij> CREATE VIEW X4 (S1, S2, ID) AS
  SELECT SUBSTR (C1, 6),
         SUBSTR (C1, 2, 4), ID
  FROM MOREGRUB;
0 rows inserted/updated/deleted
ij> -- PASS:0835 If view is created?
   COMMIT WORK;
ij> SELECT SUBSTR (CITY, 4, 10)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1         
----------
on        
ij> -- PASS:0835 If 1 row selected and value is 'on        '?
-- NOTE:0835 Right truncation subtest deleted.
   SELECT SUBSTR (CITY, 4, -1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1              
---------------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If ERROR, substring error, 0 rows selected?
   SELECT SUBSTR (CITY, 0, 10)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1         
----------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If 1 row selected and value is 'Akron     '?
-- NOTE:0835 Host language variable subtest deleted.
   SELECT SUBSTR (CITY, 1, 1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1   
----
A   
ij> -- PASS:0835 If 1 row selected and value is 'A'?
   SELECT SUBSTR (CITY, 1, 0)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1              
---------------
ij> -- PASS:0835 If 1 row selected and value is ''?
   SELECT SUBSTR (CITY, 12, 1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1   
----
ij> -- PASS:0835 If 1 row selected and value is ''?
   INSERT INTO MOREGRUB VALUES ('Pretzels', 1);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
   INSERT INTO MOREGRUB VALUES (NULL, 2);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
   INSERT INTO MOREGRUB VALUES ('Chips', 3);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
   SELECT S1 FROM X4 WHERE ID = 1;
S1        
----------
els       
ij> -- PASS:0835 If 1 row selected and S1 = 'els'?
   SELECT S1 FROM X4 WHERE ID = 3;
S1        
----------
ij> -- PASS:0835 If 1 row selected and S1 =  ''?
   SELECT S2 FROM X4 WHERE ID = 1;
S2  
----
retz
ij> -- PASS:0835 If 1 row selected and S2 = 'retz'?
   SELECT S2 FROM X4 WHERE ID = 3;
S2  
----
hips
ij> -- PASS:0835 If 1 row selected and S2 = 'hips'?
   SELECT SUBSTR (C1, ID)
  FROM MOREGRUB
  WHERE C1 LIKE 'Ch%';
1         
----------
ips       
ij> -- PASS:0835 If 1 row selected and value is 'ips'?
   SELECT SUBSTR (C1, 1, ID)
  FROM MOREGRUB
  WHERE C1 LIKE 'Ch%';
1         
----------
Chi       
ij> -- PASS:0835 If 1 row selected and value is 'Chi'?
-- NOTE:0835 Host language variable subtest deleted.
   SELECT S1 FROM X4 WHERE ID = 2;
S1        
----------
NULL      
ij> -- PASS:0835 If 1 row selected and S1 is NULL?
   DELETE FROM MOREGRUB;
3 rows inserted/updated/deleted
ij> INSERT INTO MOREGRUB VALUES ('Tacos', NULL);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?
   SELECT SUBSTR (C1, 1, ID)
   FROM MOREGRUB;
1         
----------
NULL      
ij> -- PASS:0835 If 1 row selected and value is NULL?
   SELECT SUBSTR (C1, ID, 1)
   FROM MOREGRUB;
1   
----
NULL
ij> -- PASS:0835 If 1 row selected and value is NULL?
   UPDATE MOREGRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> SELECT SUBSTR (C1, ID, ID)
   FROM MOREGRUB;
1         
----------
NULL      
ij> -- PASS:0835 If 1 row selected and value is NULL?
   ROLLBACK WORK;
ij> --O   DROP TABLE MOREGRUB CASCADE;
   drop view x4;
0 rows inserted/updated/deleted
ij> DROP TABLE MOREGRUB ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0835 <<< END TEST
-- *********************************************
-- TEST:0839 Composed <length expression> and SUBSTR!
   SELECT LENGTH (SUBSTR
  (CITY, 4, 4))
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1          
-----------
4          
ij> -- PASS:0839 If 1 row selected and value is 4?
   SELECT LENGTH (SUBSTR
  (EMPNUM, 1))
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1          
-----------
3          
ij> -- PASS:0839 If 1 row selected and value is 3?
   COMMIT WORK;
ij> -- END TEST >>> 0839 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>