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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users