gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/rowvalue8.test (about) 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