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