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 DML023
-- SQL Test Suite, V6.0, Interactive SQL, dml023.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:0103 Subquery with comparison predicate!
SELECT PNUM
FROM PROJ
WHERE PROJ.CITY =
(SELECT STAFF.CITY
FROM STAFF
WHERE EMPNUM = 'E1');
PN&
---
P1
P4
P6
ij> -- PASS:0103 If 3 rows are selected with PNUMs:'P1','P4','P6?
-- END TEST >>> 0103 <<< END TEST
-- **************************************************************
-- TEST:0104 SQLCODE < 0, subquery with more than 1 value!
SELECT PNUM
FROM PROJ
WHERE PROJ.CITY =
(SELECT STAFF.CITY
FROM STAFF
WHERE EMPNUM > 'E1' );
ERROR 21000: Scalar subquery is only allowed to return a single row.
ij> -- PASS:0104 If ERROR, SELECT returns more than 1 row in subquery?
-- PASS:0104 If 0 rows are selected?
-- END TEST >>> 0104 <<< END TEST
-- ************************************************************
-- TEST:0105 Subquery in comparison predicate is empty!
--O SELECT COUNT(*)
SELECT *
FROM STAFF
WHERE STAFF.CITY =
(SELECT PROJ.CITY
FROM PROJ
WHERE PNUM > 'P7');
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
ij> -- PASS:0105 If count = 0?
--O SELECT COUNT(*)
SELECT *
FROM STAFF
WHERE NOT (STAFF.CITY =
(SELECT PROJ.CITY
FROM PROJ
WHERE PNUM > 'P7' ));
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
ij> -- PASS:0105 If count = 0?
-- END TEST >>> 0105 <<< END TEST
-- *************************************************************
-- TEST:0106 Comparison predicate <> !
SELECT PNUM
FROM PROJ
WHERE CITY <> 'Deale';
PN&
---
P2
P3
P5
ij> -- PASS:0106 If 3 rows are selected with PNUMs:'P2','P3','P5'?
-- END TEST >>> 0106 <<< END TEST
-- *************************************************************
-- TEST:0107 Comp predicate with short string logically blank padded!
--O SELECT COUNT(*)
SELECT empnum
FROM WORKS
WHERE EMPNUM = 'E1';
EM&
---
E1
E1
E1
E1
E1
E1
ij> -- PASS:0107 If count = 6 ?
--O SELECT COUNT(*)
SELECT empnum
FROM WORKS
WHERE EMPNUM = 'E1' AND EMPNUM = 'E1 ';
EM&
---
E1
E1
E1
E1
E1
E1
ij> -- PASS:0107 If count = 6?
-- END TEST >>> 0107 <<< END TEST
-- ****************************************************************
-- TEST:0180 NULLs sort together in ORDER BY!
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0180 If 3 rows are updated?
SELECT EMPNUM,GRADE
FROM STAFF
ORDER BY GRADE,EMPNUM;
EM&|GRADE
---------
E2 |10
E4 |12
E1 |NULL
E3 |NULL
E5 |NULL
ij> -- PASS:0180 If 5 rows are selected with NULLs together ?
-- PASS:0180 If first EMPNUM is either 'E1' or 'E2'?
-- PASS:0180 If last EMPNUM is either 'E4' or 'E5?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0180 <<< END TEST
-- ***************************************************************
-- TEST:0181 NULLs are equal for DISTINCT!
-- setup
UPDATE STAFF
SET GRADE = NULL
WHERE EMPNUM = 'E1' OR EMPNUM = 'E3' OR EMPNUM = 'E5';
3 rows inserted/updated/deleted
ij> -- PASS:0181 If 3 rows are updated?
SELECT DISTINCT USER, GRADE
FROM STAFF
ORDER BY GRADE;
1 |GRADE
--------------------------------------------------------------------------------------------------------------------------------------
HU |10
HU |12
HU |NULL
ij> -- PASS:0181 If 3 rows are selected with GRADEs:10, 12, NULL ?
-- PASS:0181 GRADE 10 precedes GRADE 12?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0181 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
