ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- AUTOCOMMIT OFF; ij> --O-- MODULE CTS5TAB --O --O-- NIST SQL Test Suite, V6.0, Interactive SQL, cts5tab.sql --O-- 59-byte ID --O-- TEd Version # --O --O-- AUTHORIZATION CTS1 CREATE SCHEMA CTS1; ERROR X0Y68: Schema 'CTS1' already exists. ij> set schema CTS1; 0 rows inserted/updated/deleted ij> --O --O SELECT USER FROM HU.ECCO; VALUES USER; 1 -------------------------------------------------------------------------------------------------------------------------------- CTS1 ij> --O-- RERUN if USER value does not match preceding AUTHORIZATION comment --O --O-- date_time print --O --O ROLLBACK WORK; --O DELETE FROM CTS1.ECCO; 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> INSERT INTO CTS1.ECCO VALUES ('NL'); 1 row inserted/updated/deleted ij> DELETE FROM STAFF; 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> INSERT INTO STAFF VALUES ('E1','Alice',12,'Deale'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF VALUES ('E2','Betty',10,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF VALUES ('E3','Carmen',13,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF VALUES ('E4','Don',12,'Deale'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF VALUES ('E5','Ed',13,'Akron'); 1 row inserted/updated/deleted ij> DELETE FROM WORKS; 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> INSERT INTO WORKS VALUES ('E1','P1',40); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E1','P2',20); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E1','P3',80); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E1','P4',20); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E1','P5',12); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E1','P6',12); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E2','P1',40); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E2','P2',80); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E3','P2',20); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E4','P2',20); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E4','P4',40); 1 row inserted/updated/deleted ij> INSERT INTO WORKS VALUES ('E4','P5',80); 1 row inserted/updated/deleted ij> DELETE FROM VTABLE; 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> INSERT INTO VTABLE VALUES(10,+20,30,40,10.50); 1 row inserted/updated/deleted ij> INSERT INTO VTABLE VALUES(0,1,2,3,4.25); 1 row inserted/updated/deleted ij> INSERT INTO VTABLE VALUES(100,200,300,400,500.01); 1 row inserted/updated/deleted ij> INSERT INTO VTABLE VALUES(1000,-2000,3000,NULL,4000.00); 1 row inserted/updated/deleted ij> DELETE FROM CTS1.STAFFb; 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> INSERT INTO CTS1.STAFFb VALUES (10000,'Fred',40,'P2','Vienna','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (10000,'Fred',40,'P2','Vienna','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (10000,'Fred',40,'P2','Vienna','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (15000,'Carmen',35,'P2','Vienna','F'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (15000,'Carmen',35,'P2','Vienna','F'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (10000,'Alice',40,'P3','Prague','F'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (20000,'Betty',30,'P1','Deale','F'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (20000,'Betty',30,'P1','Deale','F'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (40000,'Don',70,'P3','Prague','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (40000,'Don',70,'P3','Prague','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (40000,'Don',70,'P3','Prague','M'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFb VALUES (10000,'Ed',40,'P1','Deale','M'); 1 row inserted/updated/deleted ij> DELETE FROM CTS1.STAFFa; 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> INSERT INTO CTS1.STAFFa VALUES (40,10000,'E6',2,'Fred'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (40,10000,'E6',2,'Fred'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (40,10000,'E1',3,'Alice'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (40,10000,'E1',3,'Alice'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (70,40000,'E4',3,'Don'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (70,40000,'E4',3,'Don'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (70,40000,'E4',3,'Don'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (30,20000,'E2',1,'Betty'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (60,45000,'E7',4,'Grace'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (60,45000,'E7',4,'Grace'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (30,8000,'E8',2,'Henry'); 1 row inserted/updated/deleted ij> INSERT INTO CTS1.STAFFa VALUES (15,7000,'E9',1,'Imogen'); 1 row inserted/updated/deleted ij> DELETE FROM STAFFc; 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> INSERT INTO STAFFc VALUES ('E1','Alice',12,'Deale',NULL); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E2','Betty',10,'Vienna','E1'); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E3','Carmen',13,'Vienna','E2'); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E4','Don',12,'Deale','E2'); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E5','Don',12,'Deale','E1'); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E6','Tom',14,'Gettysburg','E5'); 1 row inserted/updated/deleted ij> INSERT INTO STAFFc VALUES ('E7','Kingdom',18,'Gettysburg','E7'); 1 row inserted/updated/deleted ij> DELETE FROM STAFF_CTS2; 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> INSERT INTO STAFF_CTS2 VALUES ('E1','Alice',12,'Deale'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E1','Alice',12,'Deale'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E2','Betty',10,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E3','Carmen',13,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E3','Carmen',13,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E3','Carmen',13,'Vienna'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E4','Don',12,'Deale'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS2 VALUES ('E6','Don',12,'Deale'); 1 row inserted/updated/deleted ij> DELETE FROM STAFF_CTS; 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> INSERT INTO STAFF_CTS VALUES ('P1','Deale',12,'Don'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS VALUES ('P3','Vienna',10,'Betty'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS VALUES ('P3','Vienna',10,'Betty'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS VALUES ('P4','Vienna',13,'Carmen'); 1 row inserted/updated/deleted ij> INSERT INTO STAFF_CTS VALUES ('P5','Prague',15,'Ed'); 1 row inserted/updated/deleted ij> DELETE FROM EMPLOYEES2; 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> INSERT INTO EMPLOYEES2 VALUES ('Atherton',1); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Botham',2); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Cowdrey',3); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Dev',4); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Edmunds',5); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Fields',6); 1 row inserted/updated/deleted ij> INSERT INTO EMPLOYEES2 VALUES ('Gower',7); 1 row inserted/updated/deleted ij> DELETE FROM A; 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> INSERT INTO A VALUES (1, 15); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (3, 17); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (7, 11); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (5, 9); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (13, 13); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (9, 5); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (11, 7); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (17, 3); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (15, 1); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (13, 5); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (13, 15); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (11, 1); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (5, 5); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (5, 3); 1 row inserted/updated/deleted ij> INSERT INTO A VALUES (1, 1); 1 row inserted/updated/deleted ij> DELETE FROM TT; 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> INSERT INTO TT (TTA, TTC) VALUES (1, 99); 1 row inserted/updated/deleted ij> INSERT INTO TT (TTA, TTB) VALUES (2, 98); 1 row inserted/updated/deleted ij> INSERT INTO TT VALUES (3, 97, 96); 1 row inserted/updated/deleted ij> INSERT INTO TT (TTA) VALUES (4); 1 row inserted/updated/deleted ij> INSERT INTO TT VALUES (5, 42, 26); 1 row inserted/updated/deleted ij> DELETE FROM TU; 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> INSERT INTO TU VALUES ('ab', 3); 1 row inserted/updated/deleted ij> INSERT INTO TU (TUE) VALUES (5); 1 row inserted/updated/deleted ij> INSERT INTO TU VALUES ('cd', 4); 1 row inserted/updated/deleted ij> INSERT INTO TU (TUE) VALUES (11); 1 row inserted/updated/deleted ij> INSERT INTO TU VALUES ('ef', 12); 1 row inserted/updated/deleted ij> INSERT INTO TU VALUES ('gh', 11); 1 row inserted/updated/deleted ij> --O DELETE FROM TT2; --O INSERT INTO TT2 VALUES (1,INTERVAL '17-3' YEAR TO MONTH,13); --O --O INSERT INTO TT2 (TTA,TTB) VALUES (2,INTERVAL '5-6' YEAR TO MONTH); --O --O INSERT INTO TT2 (TTA) VALUES (3); --O --O INSERT INTO TT2 (TTA,TTC) VALUES (4,20); --O --O INSERT INTO TT2 VALUES (5,INTERVAL '60-2' YEAR TO MONTH,19); --O DELETE FROM TV; 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> INSERT INTO TV VALUES (1,'a'); 1 row inserted/updated/deleted ij> INSERT INTO TV VALUES (2,'b'); 1 row inserted/updated/deleted ij> INSERT INTO TV VALUES (3,'c'); 1 row inserted/updated/deleted ij> INSERT INTO TV VALUES (4,'d'); 1 row inserted/updated/deleted ij> INSERT INTO TV VALUES (5,'e'); 1 row inserted/updated/deleted ij> DELETE FROM TW; 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> INSERT INTO TW VALUES ('b',2); 1 row inserted/updated/deleted ij> INSERT INTO TW VALUES ('g',1); 1 row inserted/updated/deleted ij> INSERT INTO TW VALUES ('f',2); 1 row inserted/updated/deleted ij> INSERT INTO TW VALUES ('h',4); 1 row inserted/updated/deleted ij> INSERT INTO TW VALUES ('i',5); 1 row inserted/updated/deleted ij> DELETE FROM TX; 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> INSERT INTO TX (TX1, TX3) VALUES (1, 'Susan'); 1 row inserted/updated/deleted ij> INSERT INTO TX (TX1, TX2) VALUES (2, 'lemon'); 1 row inserted/updated/deleted ij> INSERT INTO TX VALUES (3, 'apple', ''); 1 row inserted/updated/deleted ij> INSERT INTO TX VALUES (4, 'melon', 'Peter'); 1 row inserted/updated/deleted ij> INSERT INTO TX VALUES (5, 'peach', 'Matthew'); 1 row inserted/updated/deleted ij> DELETE FROM TEST12849B; 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> INSERT INTO TEST12849B VALUES (1); 1 row inserted/updated/deleted ij> INSERT INTO TEST12849B VALUES (2); 1 row inserted/updated/deleted ij> INSERT INTO TEST12849B VALUES (3); 1 row inserted/updated/deleted ij> INSERT INTO TEST12849B VALUES (4); 1 row inserted/updated/deleted ij> DELETE FROM TABX760; 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> INSERT INTO TABX760 VALUES (10,'SPYROS',25000); 1 row inserted/updated/deleted ij> INSERT INTO TABX760 VALUES (11,'ALEXIS',18000); 1 row inserted/updated/deleted ij> INSERT INTO TABX760 VALUES (12,'LAMBIS',9000); 1 row inserted/updated/deleted ij> INSERT INTO TABX760 VALUES (13,'ELENI',4000); 1 row inserted/updated/deleted ij> INSERT INTO TABX760 VALUES (14,'MARIOS',47000); 1 row inserted/updated/deleted ij> INSERT INTO TABX760 VALUES (15,'NICKOLAS',78000); 1 row inserted/updated/deleted ij> --O DELETE FROM TABCS; --O --O INSERT INTO CTS1.TABCS VALUES (1, _CS 'NICKOS', _CS 'MARIA'); --O --O INSERT INTO CTS1.TABCS VALUES (2, _CS 'BILL', _CS 'TAKIS'); --O --O INSERT INTO CTS1.TABCS VALUES (3, _CS 'NICKOS', _CS 'TAKIS'); --O --O INSERT INTO CTS1.TABCS VALUES (4, _CS 'MARIA', _CS 'TAKIS'); --O --O DELETE FROM TTIME3; --O --O INSERT INTO CTS1.TTIME3 VALUES ( --O 1,TIME( '23:05:00'),TIME( '12:15:00-11:45'), --O TIMESTAMP( '1995-07-07 08:15:00+03:05')); --O --O INSERT INTO CTS1.TTIME3 VALUES( --O 2,TIME( '05:10:00'),TIME( '00:07:00+08:39'), --O TIMESTAMP( '2000-10-09 03:03:00+04:05')); --O --O INSERT INTO CTS1.TTIME3 VALUES ( --O 3,TIME( '12:11:00'),TIME( '23:19:00+10:32'), --O TIMESTAMP( '1997-01-16 12:17:00-12:16')); --O --O INSERT INTO CTS1.TTIME3 VALUES ( --O 4,TIME( '05:10:00'),TIME( '00:07:00+08:39'), --O TIMESTAMP( '2000-10-09 03:03:00+04:05')); --O --O INSERT INTO CTS1.TTIME3 VALUES ( --O 5,TIME( '17:39:00'),TIME( '08:28:00-11:45'), --O TIMESTAMP( '1994-12-31 20:00:00+04:05')); --O --O INSERT INTO CTS1.TTIME3 VALUES ( --O 6,TIME( '17:39:00'),TIME( '08:28:00-11:45'), --O TIMESTAMP( '1994-12-31 20:00:00+04:05')); DELETE FROM CL_STANDARD; 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> INSERT INTO CL_STANDARD VALUES (1000,'NICKOS',4000,'ATHENS'); 1 row inserted/updated/deleted ij> INSERT INTO CL_STANDARD VALUES (1001,'MARIA',4001,'RHODES'); 1 row inserted/updated/deleted ij> INSERT INTO CL_STANDARD VALUES (1002,'MAKIS',4002,'HANIA'); 1 row inserted/updated/deleted ij> INSERT INTO CL_STANDARD VALUES (1003,'GEORGE',4003,'ARTA'); 1 row inserted/updated/deleted ij> INSERT INTO CL_STANDARD VALUES (1004,'MORRIS',4004,'PARGA'); 1 row inserted/updated/deleted ij> DELETE FROM TABLE728a; 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> INSERT INTO TABLE728a VALUES ('NICKOS','GEORGE'); 1 row inserted/updated/deleted ij> INSERT INTO TABLE728a VALUES ('HARRY','TANIA'); 1 row inserted/updated/deleted ij> INSERT INTO TABLE728a VALUES ('KILLER',NULL); 1 row inserted/updated/deleted ij> INSERT INTO TABLE728a VALUES (NULL,NULL); 1 row inserted/updated/deleted ij> --O DELETE FROM TAB734; --O --O INSERT INTO CTS1.TAB734 VALUES (N' !',N'* *'); --O --O INSERT INTO CTS1.TAB734 VALUES (N' * ',N'+ +'); --O --O INSERT INTO CTS1.TAB734 VALUES (N'+ +',N'+ +'); --O --O INSERT INTO CTS1.TAB734 VALUES (NULL,N' + '); --O DELETE FROM CL_DATA_TYPE; 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> INSERT INTO CL_DATA_TYPE VALUES ('GEORGE',1,10,100); 1 row inserted/updated/deleted ij> INSERT INTO CL_DATA_TYPE VALUES ('NICK',2,20,200); 1 row inserted/updated/deleted ij> INSERT INTO CL_DATA_TYPE VALUES ('PAUL',3,30,300); 1 row inserted/updated/deleted ij> INSERT INTO CL_DATA_TYPE VALUES ('PETER',4,40,400); 1 row inserted/updated/deleted ij> INSERT INTO CL_DATA_TYPE VALUES ('KEVIN',5,50,500); 1 row inserted/updated/deleted ij> INSERT INTO CL_DATA_TYPE VALUES ('JASON',6,60,600); 1 row inserted/updated/deleted ij> COMMIT WORK; ij> --O-- *************************************************////END-OF-MODULE ; ij>