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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML081
-- SQL Test Suite, V6.0, Interactive SQL, dml081.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SCHANZLE
set schema SCHANZLE;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
SCHANZLE
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE ********************* instead ***************************
-- NOTE If a statement raises an exception condition,
-- NOTE then the system shall display a message indicating that
-- NOTE the statement failed, giving a textual description
-- NOTE of the failure.
-- NOTE If a statement raises a completion condition that is a
-- NOTE "warning" or "no data", then the system shall display
-- NOTE a message indicating that the statement completed,
-- NOTE giving a textual description of the "warning" or "no data."
-- TEST:0487 SQLSTATE 00000: successful completion!
--O SELECT COUNT(*)
SELECT *
FROM HU.WORKS;
EM&|PN&|HOURS
--------------
E1 |P1 |40
E1 |P2 |20
E1 |P3 |80
E1 |P4 |20
E1 |P5 |12
E1 |P6 |12
E2 |P1 |40
E2 |P2 |80
E3 |P2 |20
E4 |P2 |20
E4 |P4 |40
E4 |P5 |80
ij> -- PASS:0487 If count = 12?
-- PASS:0487 OR SQLSTATE = 00000: successful completion?
-- END TEST >>> 0487 <<< END TEST
-- *********************************************
-- TEST:0488 SQLSTATE 21000: cardinality violation!
--O SELECT COUNT(*)
SELECT *
FROM HU.WORKS
WHERE PNUM = (SELECT PNUM
FROM HU.WORKS
WHERE HOURS = 80);
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- PASS:0488 If ERROR, cardinality violation, 0 rows selected?
-- PASS:0488 OR SQLSTATE = 21000 OR SQLCODE < 0?
-- END TEST >>> 0488 <<< END TEST
-- *********************************************
-- TEST:0489 SQLSTATE 02000: no data!
SELECT GRADE
FROM HU.STAFF
WHERE EMPNUM = 'xx';
GRADE
-----
ij> -- PASS:0489 If 0 rows selected?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
DELETE FROM HU.STAFF
WHERE GRADE = 11;
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> -- PASS:0489 If 0 rows deleted?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
INSERT INTO HU.STAFF (EMPNUM,GRADE)
SELECT EMPNUM, 9
FROM HU.WORKS
WHERE PNUM = 'x9';
0 rows inserted/updated/deleted
ij> -- PASS:0489 If 0 rows inserted?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
UPDATE HU.STAFF SET CITY = 'Ho'
WHERE GRADE = 15;
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> -- PASS:0489 If 0 rows updated?
-- PASS:0489 OR SQLSTATE = 02000: no data OR SQLCODE = 100?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0489 <<< END TEST
-- *********************************************
-- TEST:0490 SQLSTATE 22012: data exception/division by zero!
-- setup
INSERT INTO HU.STAFF
VALUES ('E6','Fidel',0,'Havana');
1 row inserted/updated/deleted
ij> --O SELECT COUNT(*)
SELECT *
FROM HU.STAFF
WHERE EMPNAME = 'Fidel' AND 16/GRADE > 2;
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
SELECT 16/GRADE
FROM HU.STAFF
WHERE EMPNAME = 'Fidel';
1
----------------------------------
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
SELECT COUNT(*)
FROM HU.STAFF
GROUP BY CITY HAVING SUM(GRADE/0) > 44;
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
--O SELECT COUNT(*)
SELECT *
FROM HU.STAFF WHERE GRADE =
(SELECT 16/GRADE
FROM HU.STAFF
WHERE EMPNUM = 'E6');
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows selected?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
UPDATE HU.STAFF
SET GRADE = GRADE/0
WHERE GRADE = 12;
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows updated?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
INSERT INTO HU.STAFF
SELECT 'X','Y',HOURS/0,'z'
FROM HU.WORKS
WHERE PNUM = 'P6';
ERROR 22012: Attempt to divide by zero.
ij> -- PASS:0490 If ERROR, data exception/division by zero, 0 rows inserted?
-- PASS:0490 OR SQLSTATE = 22012 OR SQLCODE < 0?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0490 <<< END TEST
-- *********************************************
-- NO_TEST:0502 SQLSTATE 24000: invalid cursor state!
-- Testing cursors
-- *************************************************////END-OF-MODULE
;
ij>
|
