Re: SQL CASE WHEN THEN ELSE END

"Keith Medcalf" <[email protected]>
Newsgroups gmane.comp.db.sqlite.general
Organization This Side Up
Message-ID <[email protected]> 
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 <[email protected]> On
>Behalf Of Keith Medcalf
>Sent: Monday, 27 January, 2020 00:28
>To: SQLite mailing list <[email protected]>
>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 <[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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.