OLD | NEW |
(Empty) | |
| 1 # 2016-08-22 |
| 2 # |
| 3 # The author disclaims copyright to this source code. In place of |
| 4 # a legal notice, here is a blessing: |
| 5 # |
| 6 # May you do good and not evil. |
| 7 # May you find forgiveness for yourself and forgive others. |
| 8 # May you share freely, never taking more than you give. |
| 9 # |
| 10 #*********************************************************************** |
| 11 # Use of row values in CASE statements. |
| 12 # |
| 13 |
| 14 set testdir [file dirname $argv0] |
| 15 source $testdir/tester.tcl |
| 16 set ::testprefix rowvalue8 |
| 17 |
| 18 do_execsql_test 1.1 { |
| 19 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d); |
| 20 INSERT INTO t1(a,b,c,d) VALUES |
| 21 (1,1,2,3), |
| 22 (2,2,3,4), |
| 23 (3,1,2,4), |
| 24 (4,2,3,5), |
| 25 (5,3,4,6), |
| 26 (6,4,5,9); |
| 27 SELECT a, CASE (b,c) WHEN (1,2) THEN 'aleph' |
| 28 WHEN (2,3) THEN 'bet' |
| 29 WHEN (3,4) THEN 'gimel' |
| 30 ELSE '-' END, |
| 31 '|' |
| 32 FROM t1 |
| 33 ORDER BY a; |
| 34 } {1 aleph | 2 bet | 3 aleph | 4 bet | 5 gimel | 6 - |} |
| 35 do_execsql_test 1.2 { |
| 36 SELECT a, CASE (b,c,d) WHEN (1,2,3) THEN 'aleph' |
| 37 WHEN (2,3,4) THEN 'bet' |
| 38 WHEN (3,4,6) THEN 'gimel' |
| 39 ELSE '-' END, |
| 40 '|' |
| 41 FROM t1 |
| 42 ORDER BY a; |
| 43 } {1 aleph | 2 bet | 3 - | 4 - | 5 gimel | 6 - |} |
| 44 |
| 45 do_execsql_test 2.1 { |
| 46 CREATE TABLE t2(x INTEGER PRIMARY KEY, y); |
| 47 INSERT INTO t2(x,y) VALUES(1,6),(2,5),(3,4),(4,3),(5,2),(6,1); |
| 48 SELECT x, CASE (SELECT b,c FROM t1 WHERE a=y) |
| 49 WHEN (1,2) THEN 'aleph' |
| 50 WHEN (2,3) THEN 'bet' |
| 51 WHEN (3,4) THEN 'gimel' |
| 52 ELSE '-' END, |
| 53 '|' |
| 54 FROM t2 |
| 55 ORDER BY +x; |
| 56 } {1 - | 2 gimel | 3 bet | 4 aleph | 5 bet | 6 aleph |} |
| 57 |
| 58 |
| 59 finish_test |
OLD | NEW |