Re: SQL CASE WHEN THEN ELSE END

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