3
-- Test the case statement
6
CREATE TABLE CASE_TBL (
11
CREATE TABLE CASE2_TBL (
16
INSERT INTO CASE_TBL VALUES (1, 10.1);
17
INSERT INTO CASE_TBL VALUES (2, 20.2);
18
INSERT INTO CASE_TBL VALUES (3, -30.3);
19
INSERT INTO CASE_TBL VALUES (4, NULL);
21
INSERT INTO CASE2_TBL VALUES (1, -1);
22
INSERT INTO CASE2_TBL VALUES (2, -2);
23
INSERT INTO CASE2_TBL VALUES (3, -3);
24
INSERT INTO CASE2_TBL VALUES (2, -4);
25
INSERT INTO CASE2_TBL VALUES (1, NULL);
26
INSERT INTO CASE2_TBL VALUES (NULL, -6);
29
-- Simplest examples without tables
37
SELECT '<NULL>' AS "One",
40
END AS "Simple default";
52
END AS "ELSE default";
59
END AS "Two WHEN with default";
61
-- Constant-expression folding shouldn't evaluate unreachable subexpressions
62
SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END;
63
SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END;
65
-- However we do not currently suppress folding of potentially
66
-- reachable subexpressions
67
SELECT CASE WHEN i > 100 THEN 1/0 ELSE 0 END FROM case_tbl;
69
-- Test for cases involving untyped literals in test expression
70
SELECT CASE 'a' WHEN 'a' THEN 1 ELSE 2 END;
73
-- Examples of targets involving tables
83
CASE WHEN i >= 3 THEN (i + i)
85
END AS "Simplest Math"
88
SELECT '' AS "Five", i AS "Value",
89
CASE WHEN (i < 0) THEN 'small'
90
WHEN (i = 0) THEN 'zero'
91
WHEN (i = 1) THEN 'one'
92
WHEN (i = 2) THEN 'two'
98
CASE WHEN ((i < 0) or (i < 0)) THEN 'small'
99
WHEN ((i = 0) or (i = 0)) THEN 'zero'
100
WHEN ((i = 1) or (i = 1)) THEN 'one'
101
WHEN ((i = 2) or (i = 2)) THEN 'two'
107
-- Examples of qualifications involving tables
111
-- NULLIF() and COALESCE()
112
-- Shorthand forms for typical CASE constructs
113
-- defined in the SQL92 standard.
116
SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4;
118
SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2;
120
SELECT COALESCE(a.f, b.i, b.j)
121
FROM CASE_TBL a, CASE2_TBL b;
124
FROM CASE_TBL a, CASE2_TBL b
125
WHERE COALESCE(a.f, b.i, b.j) = 2;
127
SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)",
128
NULLIF(b.i, 4) AS "NULLIF(b.i,4)"
129
FROM CASE_TBL a, CASE2_TBL b;
131
SELECT '' AS "Two", *
132
FROM CASE_TBL a, CASE2_TBL b
133
WHERE COALESCE(f,b.i) = 2;
136
-- Examples of updates involving tables
140
SET i = CASE WHEN i >= 3 THEN (- i)
143
SELECT * FROM CASE_TBL;
146
SET i = CASE WHEN i >= 2 THEN (2 * i)
149
SELECT * FROM CASE_TBL;
152
SET i = CASE WHEN b.i >= 2 THEN (2 * j)
155
WHERE j = -CASE_TBL.i;
157
SELECT * FROM CASE_TBL;
164
DROP TABLE CASE2_TBL;