|
1 | | -QUERY: SELECT '' AS "One", |
| 1 | +QUERY: CREATE TABLE CASE_TBL ( |
| 2 | + i integer, |
| 3 | + f double precision |
| 4 | +); |
| 5 | +QUERY: CREATE TABLE CASE2_TBL ( |
| 6 | + i integer, |
| 7 | + j integer |
| 8 | +); |
| 9 | +QUERY: INSERT INTO CASE_TBL VALUES (1, 10.1); |
| 10 | +QUERY: INSERT INTO CASE_TBL VALUES (2, 20.2); |
| 11 | +QUERY: INSERT INTO CASE_TBL VALUES (3, -30.3); |
| 12 | +QUERY: INSERT INTO CASE_TBL VALUES (4, NULL); |
| 13 | +QUERY: INSERT INTO CASE2_TBL VALUES (1, -1); |
| 14 | +QUERY: INSERT INTO CASE2_TBL VALUES (2, -2); |
| 15 | +QUERY: INSERT INTO CASE2_TBL VALUES (3, -3); |
| 16 | +QUERY: INSERT INTO CASE2_TBL VALUES (2, -4); |
| 17 | +QUERY: INSERT INTO CASE2_TBL VALUES (1, NULL); |
| 18 | +QUERY: INSERT INTO CASE2_TBL VALUES (NULL, -6); |
| 19 | +QUERY: SELECT '3' AS "One", |
2 | 20 | CASE |
3 | 21 | WHEN 1 < 2 THEN 3 |
4 | | - END AS "One only = 3"; |
5 | | -One|One only = 3 |
6 | | ----+------------ |
7 | | - | 3 |
| 22 | + END AS "Simple WHEN"; |
| 23 | +One|Simple WHEN |
| 24 | +---+----------- |
| 25 | +3| 3 |
8 | 26 | (1 row) |
9 | 27 |
|
10 | | -QUERY: SELECT '' AS "One", |
| 28 | +QUERY: SELECT '<NULL>' AS "One", |
11 | 29 | CASE |
12 | 30 | WHEN 1 > 2 THEN 3 |
13 | | - END AS "One only = Null"; |
14 | | -One|One only = Null |
15 | | ----+--------------- |
16 | | - | |
| 31 | + END AS "Simple default"; |
| 32 | +One |Simple default |
| 33 | +------+-------------- |
| 34 | +<NULL>| |
17 | 35 | (1 row) |
18 | 36 |
|
19 | | -QUERY: SELECT '' AS "One", |
| 37 | +QUERY: SELECT '3' AS "One", |
20 | 38 | CASE |
21 | 39 | WHEN 1 < 2 THEN 3 |
22 | 40 | ELSE 4 |
23 | | - END AS "One with default = 3"; |
24 | | -One|One with default = 3 |
25 | | ----+-------------------- |
26 | | - | 3 |
| 41 | + END AS "Simple ELSE"; |
| 42 | +One|Simple ELSE |
| 43 | +---+----------- |
| 44 | +3| 3 |
27 | 45 | (1 row) |
28 | 46 |
|
29 | | -QUERY: SELECT '' AS "One", |
| 47 | +QUERY: SELECT '4' AS "One", |
30 | 48 | CASE |
31 | 49 | WHEN 1 > 2 THEN 3 |
32 | 50 | ELSE 4 |
33 | | - END AS "One withdefault = 4"; |
34 | | -One|One withdefault = 4 |
35 | | ----+-------------------- |
36 | | - | 4 |
| 51 | + END AS "ELSEdefault"; |
| 52 | +One|ELSEdefault |
| 53 | +---+------------ |
| 54 | +4| 4 |
37 | 55 | (1 row) |
38 | 56 |
|
39 | | -QUERY: SELECT '' AS "One", |
| 57 | +QUERY: SELECT '6' AS "One", |
40 | 58 | CASE |
41 | 59 | WHEN 1 > 2 THEN 3 |
42 | 60 | WHEN 4 < 5 THEN 6 |
43 | 61 | ELSE 7 |
44 | | - END AS "Two with default = 6"; |
45 | | -One|Two with default = 6 |
46 | | ----+-------------------- |
47 | | - | 6 |
| 62 | + END AS "TwoWHENwith default"; |
| 63 | +One|TwoWHENwith default |
| 64 | +---+--------------------- |
| 65 | +6| 6 |
48 | 66 | (1 row) |
49 | 67 |
|
50 | 68 | QUERY: SELECT '' AS "Five", |
51 | 69 | CASE |
52 | | - WHENf1 >= 0 THENf1 |
| 70 | + WHENi >= 0 THENi |
53 | 71 | END AS ">= 0 or Null" |
54 | | - FROMINT4_TBL; |
| 72 | + FROMCASE_TBL; |
55 | 73 | Five|>= 0 or Null |
56 | 74 | ----+------------ |
57 | | - | 0 |
58 | | - | 123456 |
59 | | - | |
60 | | - | 2147483647 |
61 | | - | |
62 | | -(5 rows) |
| 75 | + | 1 |
| 76 | + | 2 |
| 77 | + | 3 |
| 78 | + | 4 |
| 79 | +(4 rows) |
63 | 80 |
|
64 | 81 | QUERY: SELECT '' AS "Five", |
65 | | - CASE WHENf1 >= 0 THEN (f1 -f1) |
66 | | - ELSEf1 |
| 82 | + CASE WHENi >= 0 THEN (i -i) |
| 83 | + ELSEi |
67 | 84 | END AS "Simplest Math" |
68 | | - FROMINT4_TBL; |
| 85 | + FROMCASE_TBL; |
69 | 86 | Five|Simplest Math |
70 | 87 | ----+------------- |
71 | 88 | | 0 |
72 | 89 | | 0 |
73 | | - | -123456 |
74 | 90 | | 0 |
75 | | - | -2147483647 |
76 | | -(5 rows) |
| 91 | + | 0 |
| 92 | +(4 rows) |
| 93 | + |
| 94 | +QUERY: SELECT '' AS "Five", i AS "Value", |
| 95 | + CASE WHEN (i < 0) THEN 'small' |
| 96 | + WHEN (i = 0) THEN 'zero' |
| 97 | + WHEN (i = 1) THEN 'one' |
| 98 | + WHEN (i = 2) THEN 'two' |
| 99 | + ELSE 'big' |
| 100 | + END AS "Category" |
| 101 | + FROM CASE_TBL; |
| 102 | +Five|Value|Category |
| 103 | +----+-----+-------- |
| 104 | + | 1|one |
| 105 | + | 2|two |
| 106 | + | 3|big |
| 107 | + | 4|big |
| 108 | +(4 rows) |
77 | 109 |
|
78 | | -QUERY: SELECT '' AS "Five", f1 AS "Value", |
79 | | - CASE WHEN (f1 < 0) THEN 'small' |
80 | | - WHEN (f1 = 0) THEN 'zero' |
81 | | - WHEN (f1 = 1) THEN 'one' |
82 | | - WHEN (f1 = 2) THEN 'two' |
| 110 | +QUERY: /* |
| 111 | +SELECT '' AS "Five", |
| 112 | + CASE WHEN ((i < 0) or (i < 0)) THEN 'small' |
| 113 | + WHEN ((i = 0) or (i = 0)) THEN 'zero' |
| 114 | + WHEN ((i = 1) or (i = 1)) THEN 'one' |
| 115 | + WHEN ((i = 2) or (i = 2)) THEN 'two' |
83 | 116 | ELSE 'big' |
84 | 117 | END AS "Category" |
85 | | - FROM INT4_TBL; |
86 | | -Five| Value|Category |
87 | | -----+-----------+-------- |
88 | | - | 0|zero |
89 | | - | 123456|big |
90 | | - | -123456|small |
91 | | - | 2147483647|big |
92 | | - |-2147483647|small |
93 | | -(5 rows) |
| 118 | + FROM CASE_TBL; |
| 119 | +*/ |
| 120 | +SELECT * FROM CASE_TBL WHERE COALESCE(f,i) = 4; |
| 121 | +i|f |
| 122 | +-+- |
| 123 | +4| |
| 124 | +(1 row) |
| 125 | + |
| 126 | +QUERY: SELECT * FROM CASE_TBL WHERE NULLIF(f,i) = 2; |
| 127 | +i|f |
| 128 | +-+- |
| 129 | +(0 rows) |
| 130 | + |
| 131 | +QUERY: /* |
| 132 | +This crashes the backend at the moment... |
| 133 | +- thomas 1998-12-12 |
| 134 | +SELECT COALESCE(a.i, a.f, b.i, b.j) |
| 135 | + FROM CASE_TBL a, CASE2_TBL b; |
| 136 | +*/ |
| 137 | +SELECT * |
| 138 | + FROM CASE_TBL a, CASE2_TBL b |
| 139 | + WHERE COALESCE(a.i, a.f, b.i, b.j) = 4; |
| 140 | +i|f|i| j |
| 141 | +-+-+-+-- |
| 142 | +4| |1|-1 |
| 143 | +4| |2|-2 |
| 144 | +4| |3|-3 |
| 145 | +4| |2|-4 |
| 146 | +4| |1| |
| 147 | +4| | |-6 |
| 148 | +(6 rows) |
| 149 | + |
| 150 | +QUERY: /* |
| 151 | +This crashes the backend at the moment... |
| 152 | +- thomas 1998-12-12 |
| 153 | +SELECT '' AS Five, NULLIF(a.i,b.i) AS "NULLIF(a.i,b.i)", |
| 154 | + NULLIF(b.i, 4) AS "NULLIF(b.i,4)" |
| 155 | + FROM CASE_TBL a, CASE2_TBL b; |
| 156 | +*/ |
| 157 | +SELECT '' AS "Two", * |
| 158 | + FROM CASE_TBL a, CASE2_TBL b |
| 159 | + WHERE COALESCE(f,b.i) = 2; |
| 160 | +Two|i|f|i| j |
| 161 | +---+-+-+-+-- |
| 162 | + |4| |2|-2 |
| 163 | + |4| |2|-4 |
| 164 | +(2 rows) |
| 165 | + |
| 166 | +QUERY: UPDATE CASE_TBL |
| 167 | + SET i = CASE WHEN i >= 0 THEN (- i) |
| 168 | + ELSE (2 * i) END; |
| 169 | +QUERY: SELECT * FROM CASE_TBL; |
| 170 | + i| f |
| 171 | +--+----- |
| 172 | +-1| 10.1 |
| 173 | +-2| 20.2 |
| 174 | +-3|-30.3 |
| 175 | +-4| |
| 176 | +(4 rows) |
| 177 | + |
| 178 | +QUERY: UPDATE CASE_TBL |
| 179 | + SET i = CASE WHEN i >= 2 THEN (2 * i) |
| 180 | + ELSE (3 * i) END; |
| 181 | +QUERY: SELECT * FROM CASE_TBL; |
| 182 | + i| f |
| 183 | +---+----- |
| 184 | + -3| 10.1 |
| 185 | + -6| 20.2 |
| 186 | + -9|-30.3 |
| 187 | +-12| |
| 188 | +(4 rows) |
94 | 189 |
|
| 190 | +QUERY: /* |
| 191 | +This crashes the backend at the moment... |
| 192 | +- thomas 1998-12-12 |
| 193 | +UPDATE CASE_TBL |
| 194 | + SET i = CASE WHEN b.i >= 2 THEN (2 * j) |
| 195 | + ELSE (3 * j) END |
| 196 | + FROM CASE2_TBL b |
| 197 | + WHERE j = -CASE_TBL.i; |
| 198 | +SELECT * FROM CASE_TBL; |
| 199 | +*/ |
| 200 | +DROP TABLE CASE_TBL; |
| 201 | +QUERY: DROP TABLE CASE2_TBL; |
