| 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> -- MODULE CDR003 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, cdr003.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION SUN |
| set schema SUN; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM SUN.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| SUN |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| |
| -- date_time print |
| |
| -- TEST:0306 CHECK X IS NOT NULL, NOT X IS NULL are equivalent! |
| |
| -- setup |
| DELETE FROM STAFF13; |
| 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 STAFF13 |
| VALUES('E1','Alice',36,'Deale'); |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM STAFF13; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0306 If count = 1? |
| |
| INSERT INTO STAFF13 |
| VALUES('E2',NULL,36,'Newyork'); |
| ERROR 23513: The check constraint 'STAFF13_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF13"'. |
| ij> -- PASS:0306 If ERROR, check constraint, 0 rows inserted? |
| |
| SELECT COUNT(*) FROM STAFF13; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0306 If count = 1? |
| |
| -- END TEST >>> 0306 <<< END TEST |
| |
| -- ************************************************* |
| |
| |
| -- TEST:0307 CHECK <like predicate> in <tab. cons>, insert! |
| |
| -- setup |
| DELETE FROM STAFF9; |
| 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 STAFF9 |
| VALUES('E1','Thomas',0,'Deale'); |
| ERROR 23513: The check constraint 'STAFF9_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF9"'. |
| ij> -- PASS:0307 If ERROR, check constraint, 0 rows inserted? |
| |
| INSERT INTO STAFF9 |
| VALUES('E2','Tom',22,'Newyork'); |
| ERROR 23513: The check constraint 'STAFF9_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF9"'. |
| ij> -- PASS:0307 If ERROR, check constraint, 0 rows inserted? |
| |
| INSERT INTO STAFF9 |
| VALUES('E3','Susan',11,'Hawaii'); |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM STAFF9; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0307 If count = 1? |
| |
| |
| -- END TEST >>> 0307 <<< END TEST |
| |
| -- ************************************************* |
| |
| |
| -- TEST:0308 CHECK <in predicate> in <tab. cons.>, insert! |
| |
| -- setup |
| DELETE FROM STAFF10; |
| 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 STAFF10 |
| VALUES('E1','Thomas',5,'Deale'); |
| ERROR 23513: The check constraint 'STAFF10_GRADE' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF10"'. |
| ij> -- PASS:0308 If ERROR, check constraint, 0 rows inserted? |
| |
| INSERT INTO STAFF10 |
| VALUES('E2','Tom',22,'Newyork'); |
| ERROR 23513: The check constraint 'STAFF10_GRADE' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF10"'. |
| ij> -- PASS:0308 If ERROR, check constraint, 0 rows inserted? |
| |
| INSERT INTO STAFF10 |
| VALUES('E3','Susan',11,'Hawaii'); |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM STAFF10; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0308 If count = 1? |
| |
| -- END TEST >>> 0308 <<< END TEST |
| |
| -- ************************************************* |
| |
| -- NO_TEST:0373 insert with embeded var. & indic. var. CHECK clause! |
| |
| -- Testing Embedded Variable & Indicator Variable |
| |
| -- ************************************************* |
| |
| |
| -- TEST:0374 computation in update, CHECK clause! |
| |
| -- setup |
| DELETE FROM STAFF5; |
| 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 STAFF5 |
| VALUES('R9','Alice',15,'Deale'); |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM STAFF5; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0374 If count = 1? |
| |
| UPDATE STAFF5 |
| SET GRADE = 10 * 10 / 5 + 1 |
| WHERE EMPNUM = 'R9'; |
| ERROR 23513: The check constraint 'STAFF5_GRADE' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF5"'. |
| ij> -- PASS:0374 If ERROR, check constraint, 0 rows updated? |
| |
| SELECT COUNT(*) FROM STAFF5 |
| WHERE GRADE = 15; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0374 If count = 1? |
| |
| -- restore |
| ROLLBACK WORK; |
| ij> -- END TEST >>> 0374 <<< END TEST |
| |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |