I get nothing at all except a complaint that the syntax is invalid. In particular
( CASE ( SELECT WYear FROM t2 WHERE pid = a.a ) WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END ) AS DIGITAL Is not a valid scalar expression. Parsing fails at "WHEN". What exactly do you intend this scalar expression to do? (and if the syntax was not invalid, the result would always be NO since it is impossible for the result of the subselect (which will always be a 4 digit number because that is all that is in t2, or null, if not found (and a random year at that since there can be multiple rows with the same pid and you did not specify which one you want)) to be equal to the 0 or 1 (true or false) boolean expression after the WHEN. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Jose Isaias Cabrera >Sent: Sunday, 26 January, 2020 19:44 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: [sqlite] SQL CASE WHEN THEN ELSE END > > >Greetings! > >I am getting the wrong output, and I don't know how to get it to work. >Please take a look at the following (Pardon the lengthy data): >create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); >insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4, >'2019-02-11'); >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, >'2019-02-11'); >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4, >'2019-02-11'); >insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, >'2019-02-11'); >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, >'2019-02-11'); >insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, >'2019-02-12'); >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, >'2019-02-12'); >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4, >'2019-02-12'); >insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4, >'2019-02-12'); >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4, >'2019-02-12'); >insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4, >'2019-02-13'); >insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4, >'2019-02-13'); >insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4, >'2019-02-13'); >insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4, >'2019-02-13'); >insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8, >'2019-02-13'); > >create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate); >insert into t1 (f, g, h, i, j, idate) values ('p001', 1, 4, 'n', 4, >'2019-02-11'); >insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4, >'2019-02-11'); >insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4, >'2019-02-11'); >insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 5, 'y', 4, >'2019-02-11'); >insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4, >'2019-02-11'); >insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4, >'2019-02-12'); >insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4, >'2019-02-12'); >insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4, >'2019-02-12'); >insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4, >'2019-02-12'); >insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4, >'2019-02-12'); >insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4, >'2019-02-13'); >insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4, >'2019-02-13'); >insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4, >'2019-02-13'); >insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4, >'2019-02-13'); >insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8, >'2019-02-13'); > >create table t2 (pid, WYear); >insert into t2 values ('p001', 2019); >insert into t2 values ('p003', 2019); >insert into t2 values ('p004', 2019); >insert into t2 values ('p002', 2020); >insert into t2 values ('p003', 2020); >insert into t2 values ('p005', 2020); > >When I run this SELECT, > >SELECT a.a, a.c, a.e, b.g, b.h, b.i, >( > CASE > ( > SELECT WYear FROM t2 WHERE pid = a.a > ) > WHEN c.WYear = 2020 THEN “YES” > ELSE “NO” END >) AS DIGITAL >FROM t0 as a, t1 as b, t2 as c >WHERE a.a = b.f and a.a = c.pid >AND a.c = 2020 >AND a.idate = (SELECT MAX(idate) from t0 where a = a.a) >AND b.idate = (SELECT MAX(idate) from t1 where f = a.a) >GROUP BY a.a >; > >I get, > >p001|2020|4|10|1|n|NO >p003|2020|4|3|9|y|NO >p005|2020|8|5|3|y|NO > >I expected this, > >p001|2020|4|10|1|n|NO >p003|2020|4|3|9|y|YES >p005|2020|8|5|3|y|YES > >I've tried a few changes, but nothing seems to work. Thoughts please? >thanks. > >josé >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users