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