SQL CASE WHEN THEN ELSE END

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Igor Tandetnik-2
On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
>      CASE
>      (
>            SELECT WYear FROM t2 WHERE pid = a.a
>      )
>      WHEN c.WYear = 2020 THEN “YES”
>      ELSE “NO” END
> ) AS DIGITAL

This should probably be simply

case c.WYear when 2020 then 'YES' else 'NO' end

or equivalently

case when c.WYear=2020 then 'YES' else 'NO' end

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Simon Slavin-3
In reply to this post by Jose Isaias Cabrera-4
On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera <[hidden email]> wrote:

> CASE
>    (
>          SELECT WYear FROM t2 WHERE pid = a.a
>    )
>    WHEN c.WYear = 2020 THEN “YES”
>    ELSE “NO” END

That's not the structure of a CASE statement.

After CASE comes an expression.
After WHEN comes another expression.
If they equal one another, then the the bit after the THEN is returned.

You want something more like

SELECT
    (CASE WYear WHEN 2020 THEN 'YES' ELSE 'NO' END) AS DIGITAL
    FROM t2 WHERE pid = a.a

but you'll have to fit this in with how your overall SELECT works.

Sse "The CASE expression" on this page for more details:

<https://sqlite.org/lang_expr.html>

Also note that to quote strings you use a single apostrophe at both ends, not directed speech marks, just as you had in your INSERT commands.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4
In reply to this post by Igor Tandetnik-2

Igor Tandetnik, on Sunday, January 26, 2020 09:57 PM, wrote...

>
> On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
> >      CASE
> >      (
> >            SELECT WYear FROM t2 WHERE pid = a.a
> >      )
> >      WHEN c.WYear = 2020 THEN “YES”
> >      ELSE “NO” END
> > ) AS DIGITAL
>
> This should probably be simply
>
> case c.WYear when 2020 then 'YES' else 'NO' end
>
> or equivalently
>
> case when c.WYear=2020 then 'YES' else 'NO' end

Thanks, Igor.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Keith Medcalf

This version generates the most efficient query plan in 3.31.0 when you have indexes on the necessary columns:

CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the index
CREATE INDEX t1_1 on t1 (f, idate);
CREATE INDEX t2_1 on t2 (pid, wyear); -- this could be a without rowid table with both columns in the primary key

with keys (pid, idate0, idate1)
  as (
      select distinct pid,
             (
              select max(idate)
                from t0
               where a == pid
             ),
             (
              select max(idate)
                from t1
               where f == pid
             )
        from (
              select distinct pid
                from t2
             )
     )
  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, keys
   WHERE a.a == keys.pid
     AND b.f == keys.pid
     AND a.idate == keys.idate0
     AND b.idate == keys.idate1
     AND a.c == 2020
;

without help the query planner does not seem to generate a very good plan but maybe that is because the sample data is so small ... or maybe it does and I cannot tell with such small data ... but this forces the query to execute in the manner I think it should.  If you take the "distinct" from the keys select it frees up the query planner to perhaps find a better plan -- you need the "select distinct pid from t2" to prevent duplicate rows.

--
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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Monday, 27 January, 2020 00:28
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] SQL CASE WHEN THEN ELSE END
>
>
>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 <[hidden email]> On
>>Behalf Of Jose Isaias Cabrera
>>Sent: Sunday, 26 January, 2020 19:44
>>To: SQLite mailing list <[hidden email]>
>>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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3


Simon Slavin, on Sunday, January 26, 2020 09:59 PM, wrote...

>
> On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera <[hidden email]>
> wrote:
>
> > CASE
> >    (
> >          SELECT WYear FROM t2 WHERE pid = a.a
> >    )
> >    WHEN c.WYear = 2020 THEN “YES”
> >    ELSE “NO” END
>
> That's not the structure of a CASE statement.
>
> After CASE comes an expression.
> After WHEN comes another expression.
> If they equal one another, then the the bit after the THEN is returned.
>
> You want something more like
>
> SELECT
>     (CASE WYear WHEN 2020 THEN 'YES' ELSE 'NO' END) AS DIGITAL
>     FROM t2 WHERE pid = a.a
>
> but you'll have to fit this in with how your overall SELECT works.
>
> Sse "The CASE expression" on this page for more details:
>
> <https://sqlite.org/lang_expr.html>

Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

Keith Medcalf, on Monday, January 27, 2020 02:28 AM, wrote...

>
>
> 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
> ;
>

Yep, this one works also.  Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...

>
>
> This version generates the most efficient query plan in 3.31.0 when you
> have indexes on the necessary columns:
>
> CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the
> index
> CREATE INDEX t1_1 on t1 (f, idate);
> CREATE INDEX t2_1 on t2 (pid, wyear); -- this could be a without rowid
> table with both columns in the primary key

I think I already have these INDEXes, but I will make sure.  Thanks for this one also.  I love having different ways to write the code.

> with keys (pid, idate0, idate1)
>   as (
>       select distinct pid,
>              (
>               select max(idate)
>                 from t0
>                where a == pid
>              ),
>              (
>               select max(idate)
>                 from t1
>                where f == pid
>              )
>         from (
>               select distinct pid
>                 from t2
>              )
>      )
>   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, keys
>    WHERE a.a == keys.pid
>      AND b.f == keys.pid
>      AND a.idate == keys.idate0
>      AND b.idate == keys.idate1
>      AND a.c == 2020
> ;
>
> without help the query planner does not seem to generate a very good plan
> but maybe that is because the sample data is so small ... or maybe it does
> and I cannot tell with such small data ... but this forces the query to
> execute in the manner I think it should. If you take the "distinct" from
> the keys select it frees up the query planner to perhaps find a better plan
> -- you need the "select distinct pid from t2" to prevent duplicate rows.

Thanks, Keith.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4

Jose Isaias Cabrera, on Monday, January 27, 2020 08:42 AM, wrote...
>
>
> Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...

This is actually what I need:

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.c == 2020
;

Because there are other records that are in the other databases, but not in t2, which still need to be part of the result.  Thanks for everything Igor, Keith, Simon, and everyone who thought about it. ;-)

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

David Raymond
In reply to this post by Jose Isaias Cabrera-4
This is technically valid CASE syntax which is why you're not getting an error, it's just not what you're looking for.
...
CASE
(SELECT c.WYear FROM t2 WHERE pid = a.a)
WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END
) AS DIGITAL
...

What that is saying is take the value you get from this:

(SELECT c.WYear FROM t2 WHERE pid = a.a)

and compare it to the value you get from this:

(c.WYear = 2020)

and if those two values match, then return the value of the field that's named "YES" (I'm assuming you wanted 'YES' there)

(c.WYear = 2020) is a perfectly valid expression... that's returning a boolean (well, int)
So you're comparing c.WYear (from the subquery) against a boolean.


(Others have replied with improved versions of the query, but for people following at home I figured I'd try to point out why the original version parsed ok and ran, just wasn't what you intended)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL CASE WHEN THEN ELSE END

Jose Isaias Cabrera-4

David Raymond, on Monday, January 27, 2020 10:32 AM, wrote...

[clip]
> (c.WYear = 2020) is a perfectly valid expression... that's returning a
> boolean (well, int)
> So you're comparing c.WYear (from the subquery) against a boolean.

Yep, this little bit I knew. :-)

> (Others have replied with improved versions of the query, but for people
> following at home I figured I'd try to point out why the original version
> parsed ok and ran, just wasn't what you intended)

Fair enough...
The original email had inserts that would suffice the table that would create the 'YES' or the 'NO'. For example...

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);

As you can see, t2 contains data matches well with t0 and t1 regarding a, f and pid.  However, when I ran this on the real data, I found out that that there was data missing, ie.

insert into t0 (a, b, c, d, e, idate) values ('p006', 5, 2020, 'y', 8, '2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p007', 5, 2020, 'n', 8, '2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p008', 5, 2020, 'n', 8, '2019-03-13');

and

insert into t1 (f, g, h, i, j, idate) values ('p006', 6, 7, 'n', 8, '2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p007', 6, 7, 'n', 8, '2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p008', 6, 9, 'y', 8, '2019-03-13');

So, when I ran the original query,

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
;

it would only give me the records that were part of t2:

p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES

But, I also needed to display p006, p007, p008.  So, by taking the third to last line, "AND a.a IN (SELECT pid FROM t2)", that gave me the correct result:
sqlite> 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.c == 2020
   ...> ;
p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES
p006|2020|8|6|7|n|NO
p007|2020|8|6|7|n|NO
p008|2020|8|6|9|y|NO
sqlite>

And that's it... :-)  For those of you scoring at home:
Jose request for SQL help: 2031
Jose able to figure his own problem: 23

:-)

josé

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