Re: SQL CASE WHEN THEN ELSE END

Keith Medcalf, on Sunday, January 26, 2020 11:19 PM, wrote...
>
>
> 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".

This one does not give a syntax error and provides a result:

SELECT a.a, a.c, a.e, b.g, b.h, b.i,
(
CASE
(SELECT c.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
;

But, it gives me the wrong result.  I must have placed one of my many variations in the email with a syntax error.

> What exactly
> do you intend this scalar expression to do?

The idea is that if the pid is found in t2 with the same WYear (2020) I wanted to add YES to DIGITAL. Igor provided the solution.  For some reason I thought that I needed to give the full select to the CASE function.


> (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.

I must have given the wrong
> 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 <[email protected]> On
> >Behalf Of Jose Isaias Cabrera
> >Sent: Sunday, 26 January, 2020 19:44
> >To: SQLite mailing list <[email protected]>
> >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
> >[email protected]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users