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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set ::testprefix rowvalue8 17 18do_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 - |} 35do_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 45do_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 59finish_test 60