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
178
179
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML059
-- SQL Test Suite, V6.0, Interactive SQL, dml059.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION HU
set schema HU;
0 rows inserted/updated/deleted
ij> --0 SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
HU
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- TEST:0257 SELECT MAX, MIN (COL1 + or - COL2)!
-- setup
INSERT INTO VTABLE
VALUES(10,11,12,13,15);
1 row inserted/updated/deleted
ij> -- PASS:0257 If 1 row is inserted?
-- setup
INSERT INTO VTABLE
VALUES(100,111,1112,113,115);
1 row inserted/updated/deleted
ij> -- PASS:0257 If 1 row is inserted?
SELECT COL1, MAX(COL2 + COL3), MIN(COL3 - COL2)
FROM VTABLE
GROUP BY COL1
ORDER BY COL1;
COL1 |2 |3
-----------------------------------
0 |3 |1
10 |50 |1
100 |1223 |100
1000 |1000 |5000
ij> -- PASS:0257 If 4 rows are selected in order with values:?
-- PASS:0257 ( 0, 3, 1) ?
-- PASS:0257 ( 10, 50, 1)?
-- PASS:0257 ( 100, 1223, 100)?
-- PASS:0257 ( 1000, 1000, 5000)?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0257 <<< END TEST
-- *********************************************************************
-- TEST:0258 SELECT SUM(2*COL1*COL2) in HAVING SUM(COL2*COL3)!
-- setup
INSERT INTO VTABLE
VALUES (10,11,12,13,15);
1 row inserted/updated/deleted
ij> -- PASS:0258 if 1 row is inserted?
-- setup
INSERT INTO VTABLE
VALUES (100,111,1112,113,115);
1 row inserted/updated/deleted
ij> -- PASS:0258 if 1 row is inserted ?
SELECT COL1,SUM(2 * COL2 * COL3)
FROM VTABLE
GROUP BY COL1
HAVING SUM(COL2 * COL3) > 2000
OR SUM(COL2 * COL3) < -2000
ORDER BY COL1;
COL1 |2
-----------------------
100 |366864
1000 |-12000000
ij> -- PASS:0258 If 2 rows are selected?
-- PASS:0258 If first row has values (100, 366864) ?
-- PASS:0258 If second row has values (1000, -12000000) ?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0258 <<< END TEST
-- *********************************************************************
-- TEST:0259 SOME, ANY in HAVING clause!
-- setup
INSERT INTO VTABLE
VALUES(10,11,12,13,15);
1 row inserted/updated/deleted
ij> -- PASS:0259 If 1 row is inserted?
-- setup
INSERT INTO VTABLE
VALUES(100,111,1112,113,115);
1 row inserted/updated/deleted
ij> -- PASS:0259 If 1 row is inserted?
SELECT COL1, MAX(COL2)
FROM VTABLE
GROUP BY COL1
HAVING MAX(COL2) > ANY (SELECT GRADE FROM STAFF)
AND MAX(COL2) < SOME (SELECT HOURS FROM WORKS)
ORDER BY COL1;
COL1 |2
-----------------------
10 |20
ij> -- PASS:0259 If 1 row is selected and COL1 = 10 and MAX(COL2) = 20?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0259 <<< END TEST
-- *******************************************************************
-- TEST:0260 EXISTS in HAVING clause!
-- setup
INSERT INTO VTABLE
VALUES(10,11,12,13,15);
1 row inserted/updated/deleted
ij> -- PASS:0260 If 1 row is inserted?
-- setup
INSERT INTO VTABLE
VALUES(100,111,1112,113,115);
1 row inserted/updated/deleted
ij> -- PASS:0260 If 1 row is inserted?
SELECT COL1, MAX(COL2)
FROM VTABLE
GROUP BY COL1
HAVING EXISTS (SELECT *
FROM STAFF
WHERE EMPNUM = 'E1')
AND MAX(COL2) BETWEEN 10 AND 90
ORDER BY COL1;
COL1 |2
-----------------------
10 |20
ij> -- PASS:0260 If 1 row is selected and COL1 = 10 and MAX(COL2) = 20?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0260 <<< END TEST
-- ******************************************************************
-- TEST:0264 WHERE, HAVING without GROUP BY!
SELECT SUM(COL1)
FROM VTABLE
WHERE 10 + COL1 > COL2
HAVING MAX(COL1) > 100;
1
-----------
1000
ij> -- PASS:0264 If SUM(COL1) = 1000?
SELECT SUM(COL1)
FROM VTABLE
WHERE 1000 + COL1 >= COL2
HAVING MAX(COL1) > 100;
1
-----------
1110
ij> -- PASS:0264 If SUM(COL1) = 1110?
-- END TEST >>> 0264 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
