Do you perhaps mean: SELECT a.a, a.c, a.e, b.g, b.h, b.i, coalesce(( SELECT 'YES' FROM t2 WHERE wYear == a.c AND pid == a.a ), 'NO') AS digital FROM t0 as a, t1 as b WHERE a.a == b.f AND a.idate == (SELECT MAX(idate) from t0 where a = a.a) AND b.idate == (SELECT MAX(idate) from t1 where f = a.a) AND a.a IN (SELECT pid FROM t2) AND a.c == 2020 ;
-- 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