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
