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
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML083
-- SQL Test Suite, V6.0, Interactive SQL, dml083.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."
-- NO_TEST:0496 SQLSTATE 22002: data exception/null, value, no indic.!
-- Testing indicators
-- *********************************************
-- TEST:0498 SQLSTATE 22001: data exception/string right trunc.!
INSERT INTO HU.STAFF
VALUES ('E6','Earl Brown',11,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?
INSERT INTO HU.STAFF
VALUES ('E7','Ella Brown',12,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?
--O SELECT COUNT(*)
SELECT *
FROM HU.STAFF;
EM&|EMPNAME |GRADE|CITY
----------------------------------------------
E1 |Alice |12 |Deale
E2 |Betty |10 |Vienna
E3 |Carmen |13 |Vienna
E4 |Don |12 |Deale
E5 |Ed |13 |Akron
ij> -- PASS:0498 If count = 5?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0498 <<< END TEST
-- *********************************************
-- TEST:0500 SQLSTATE 01003: warning/null value elim. in set function!
-- setup
DELETE FROM HU.HH;
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 HU.HH
VALUES (3);
1 row inserted/updated/deleted
ij> INSERT INTO HU.HH
VALUES (NULL);
1 row inserted/updated/deleted
ij> SELECT AVG(SMALLTEST)
FROM HU.HH;
1
------
3
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
-- setup
UPDATE HU.STAFF
SET GRADE = NULL
WHERE GRADE = 13;
2 rows inserted/updated/deleted
ij> SELECT AVG(GRADE)
FROM HU.STAFF
WHERE CITY = 'Vienna';
1
------------
10.0000
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
SELECT SUM(DISTINCT GRADE)
FROM HU.STAFF;
1
---------
22
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
INSERT INTO HU.HH
SELECT MAX(GRADE)
FROM HU.STAFF;
1 row inserted/updated/deleted
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
DELETE FROM HU.HH
WHERE SMALLTEST < (SELECT MIN(GRADE)
FROM HU.STAFF
WHERE CITY = 'Vienna');
1 row inserted/updated/deleted
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
SELECT CITY, COUNT(DISTINCT GRADE)
FROM HU.STAFF
GROUP BY CITY
ORDER BY CITY DESC;
CITY |2
---------------------------
Vienna |1
WARNING 01003: Null values were eliminated from the argument of a column function.
Deale |1
Akron |0
ij> -- PASS:0500 If 3 rows are selected with the following order?
-- PASS:0500 CITY COUNT(DISTINCT GRADE)?
-- PASS:0500 'Vienna' 1?
-- PASS:0500 'Deale' 1?
-- PASS:0500 'Akron' 0?
-- PASS:0500 OR SQLSTATE = 01003?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0500 <<< END TEST
-- *********************************************
-- NO_TEST:0501 SQLSTATE 01004: warning/string right truncation!
-- Testing host variables
-- *************************************************////END-OF-MODULE
;
ij>
|
