1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML009
-- SQL Test Suite, V6.0, Interactive SQL, dml009.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
-- TEST:0022 INSERT(column list) VALUES(literals and NULL)!
-- setup
INSERT INTO WORKS(PNUM,EMPNUM,HOURS)
VALUES ('P22','E22',NULL);
1 row inserted/updated/deleted
ij> -- PASS:0022 If 1 row inserted?
SELECT EMPNUM,PNUM
FROM WORKS
WHERE HOURS IS NULL;
EM&|PN&
-------
E22|P22
ij> -- PASS:0022 If EMPNUM = 'E22'?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0022 <<< END TEST
-- **************************************************************
-- TEST:0023 DEC precision >= col.def.: ERROR if left-truncate!
-- setup
DELETE FROM TEMP_S;
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> COMMIT WORK;
ij> -- setup
INSERT INTO TEMP_S(EMPNUM,GRADE,CITY)
VALUES('E23',2323.4,'China');
1 row inserted/updated/deleted
ij> -- PASS:0023 If 1 row inserted?
--O SELECT COUNT(*)
SELECT *
FROM TEMP_S;
EMP&|GRADE|CITY
--------------------------
E23 |2323 |China
ij> -- PASS:0023 If count = 1?
-- setup
INSERT INTO TEMP_S
VALUES('E23',23234,'China');
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(4,0).
ij> -- PASS:0023 If 1 row inserted or ?
-- PASS:0023 insert fails due to precision of 23234?
--O SELECT COUNT(*)
SELECT *
FROM TEMP_S;
EMP&|GRADE|CITY
--------------------------
E23 |2323 |China
ij> -- PASS:0023 If count = 1 or 2 (depending on previous insertion)?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0023 <<< END TEST
-- ***************************************************************
-- TEST:0024 INSERT:<query spec.> is empty: SQLCODE = 100!
-- setup
INSERT INTO TEMP_S
SELECT EMPNUM,GRADE,CITY
FROM STAFF
WHERE GRADE > 13;
0 rows inserted/updated/deleted
ij> -- PASS:0024 If 0 rows selected, SQLCODE = 100, end of data?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0024 <<< END TEST
-- *************************************************************
-- TEST:0025 INSERT:<query spec.> is not empty!
DELETE FROM TEMP_S;
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> -- setup
INSERT INTO TEMP_S(EMPNUM,GRADE,CITY)
SELECT EMPNUM,GRADE,CITY
FROM STAFF
WHERE GRADE > 12;
2 rows inserted/updated/deleted
ij> -- PASS:0025 If 2 rows are inserted?
--O SELECT COUNT(*)
SELECT *
FROM TEMP_S;
EMP&|GRADE|CITY
--------------------------
E3 |13 |Vienna
E5 |13 |Akron
ij> -- PASS:0025 If count = 2?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0025 <<< END TEST
-- *************************************************************
-- TEST:0026 INSERT into view with check option and unique violation!
--0 SELECT COUNT(*) FROM STAFF;
-- PASS:0026 If count = 5?
-- setup
--0 INSERT INTO TEMP_SS
--0 SELECT EMPNUM,GRADE,CITY
--0 FROM STAFF3
--0 WHERE GRADE = 10;
-- PASS:0026 If ERROR, view check constraint, 0 rows inserted OR ?
-- PASS:0026 If ERROR, unique constraint, 0 rows inserted?
--0 SELECT COUNT(*) FROM STAFF;
-- PASS:0026 If count = 5?
-- restore
--0 ROLLBACK WORK;
-- END TEST >>> 0026 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
