subject:"\[sqlite\] SQL CASE WHEN THEN ELSE END"

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread David Raymond

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera


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  On
> >Behalf Of Jose Isaias Cabrera
> >Sent: Sunday, 26 January, 2020 19:44
> >To: SQLite mailing list 
> >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,

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread Jose Isaias Cabrera



Simon Slavin, on Sunday, January 26, 2020 09:59 PM, wrote...
>
> On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera 
> 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:
>
> 

Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-27 Thread 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  On
>Behalf Of Keith Medcalf
>Sent: Monday, 27 January, 2020 00:28
>To: SQLite mailing list 
>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  On
>>Behalf Of Jose Isaias Cabrera
>>Sent: Sunday, 26 January, 2020 19:44
>>To: SQLite mailing list 
>>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,

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Keith Medcalf


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  On
>Behalf Of Jose Isaias Cabrera
>Sent: Sunday, 26 January, 2020 19:44
>To: SQLite mailing list 
>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 seem

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Keith Medcalf


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  On
>Behalf Of Jose Isaias Cabrera
>Sent: Sunday, 26 January, 2020 19:44
>To: SQLite mailing list 
>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,
>

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Simon Slavin

On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera  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:

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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Igor Tandetnik


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

[sqlite] SQL CASE WHEN THEN ELSE END

2020-01-26 Thread Jose Isaias Cabrera


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

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

Re: [sqlite] SQL CASE WHEN THEN ELSE END

[sqlite] SQL CASE WHEN THEN ELSE END

14 matches

Site Navigation

Mail list logo

Footer information