SELECT with CASE

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

Re: SELECT with CASE

Keith Medcalf

Yes.  An alternate solution might be to return the "smallest" date (assuming that today occurs before (is less than) tomorrow -- currently the case, but who knows if it will always be so ...)

SELECT MIN(CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1 day') END) as TheDate FROM Dates;

Of course, this requires that there be at least one row in the table and will return nothing if there are no rows in Dates at all.  Thus the problem statement becomes "Return todays date if it is in the table, if not return tomorrow's date, unless there are no dates in the table at all in which case return nothing".  This is not the case as the original problem statement.

It will also be somewhat (perhaps a lot) more inefficient, particularly if there are more than a trivial number of rows in your Dates table.

You can also implement COALESCE using case:

SELECT CASE WHEN (SELECT TheDate FROM Dates WHERE TheDate==date('now')) IS NOT NULL
            THEN (SELECT TheDate FROM Dates WHERE TheDate==date('now))
            ELSE date('now', '+1 day')
       END as TheDate;

However, it is not as efficient as using COALESCE, and is about two times more inefficient than the first example since now the Dates table has to be scanned twice.  It is, however, compliant with the original problem definition.


---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of Csányi Pál
>Sent: Thursday, 12 April, 2018 09:36
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>Thank you very much!
>
>Just can't understand why the CASE method does not work?
>It can't be done with the CASE expression at all?
>
>2018-04-12 17:26 GMT+02:00 Keith Medcalf <[hidden email]>:
>>
>> Then Richard is correct (of course) ... which is a perfect
>translation of the problem statement into SQL.
>>
>> SELECT COALESCE((SELECT thedate FROM dates WHERE
>thedate==date('now')), date('now','+1 day')) as TheDate;
>>
>>
>> ---
>> 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 [mailto:sqlite-users-
>>>[hidden email]] On Behalf Of Csányi Pál
>>>Sent: Thursday, 12 April, 2018 09:20
>>>To: SQLite mailing list
>>>Subject: Re: [sqlite] SELECT with CASE
>>>
>>>Yes, this is what I am asking.
>>>
>>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <[hidden email]>:
>>>>
>>>> The question you asked was:
>>>>
>>>> "Then how can I get only that date from the Dates table - which
>is
>>>> equal to the current date?"
>>>>
>>>> and you are now posing a second question:
>>>>
>>>> ">Yes, but I want the CASE because if there is no such date in
>the
>>>>>Dates
>>>>>table which is equal to the date('now') then it should return the
>>>>>date('now','+1 day')."
>>>>
>>>> Which seems like a rather long winded way of stating the problem:
>>>>
>>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>>will return, at the time the query is run, based on the comuter on
>>>which the query is run concept of today's date, today's date, if
>that
>>>date is in the table otherwise the tomorrow's date (based on the
>>>current concept of 'tomorrow' on the computer on which the query is
>>>run."
>>>>
>>>> Is this what you are asking?
>>>>
>>>> ---
>>>> 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 [mailto:sqlite-users-
>>>>>[hidden email]] On Behalf Of Csányi Pál
>>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>>To: SQLite mailing list
>>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>>
>>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <[hidden email]>:
>>>>>>
>>>>>> select TheDate from Dates where TheDate == date('now');
>>>>>
>>>>>Yes, but I want the CASE because if there is no such date in the
>>>>>Dates
>>>>>table which is equal to the date('now') then it should return the
>>>>>date('now','+1 day').
>>>>>_______________________________________________
>>>>>sqlite-users mailing list
>>>>>[hidden email]
>>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>_______________________________________________
>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: SELECT with CASE

David Raymond
In reply to this post by csanyipal
The CASE method you were using was going through the Dates table and returning 1 result <per row of that table>. Since you're looking for only 1 return row from an entire table, then you might want something different.

select case exists (select 1 from Dates where TheDate = date('now'))
  when 1 then date('now')
  else date('now', '+1 day')
  end as TheDate;

Or if you need the next day which isn't in the table:

with recursive foo (tempDate) as (
    values (date('now'))
    union all
    select date(tempDate, '+1 day') from foo
        where exists (select 1 from Dates where TheDate = tempDate)
)
select max(tempDate) as TheDate from foo;



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Csányi Pál
Sent: Thursday, April 12, 2018 11:36 AM
To: SQLite mailing list
Subject: Re: [sqlite] SELECT with CASE

Thank you very much!

Just can't understand why the CASE method does not work?
It can't be done with the CASE expression at all?

2018-04-12 17:26 GMT+02:00 Keith Medcalf <[hidden email]>:

>
> Then Richard is correct (of course) ... which is a perfect translation of the problem statement into SQL.
>
> SELECT COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')), date('now','+1 day')) as TheDate;
>
>
> ---
> 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 [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Csányi Pál
>>Sent: Thursday, 12 April, 2018 09:20
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>Yes, this is what I am asking.
>>
>>2018-04-12 17:17 GMT+02:00 Keith Medcalf <[hidden email]>:
>>>
>>> The question you asked was:
>>>
>>> "Then how can I get only that date from the Dates table - which is
>>> equal to the current date?"
>>>
>>> and you are now posing a second question:
>>>
>>> ">Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day')."
>>>
>>> Which seems like a rather long winded way of stating the problem:
>>>
>>> "I have a table with a bunch-o-dates in it.  I want a query which
>>will return, at the time the query is run, based on the comuter on
>>which the query is run concept of today's date, today's date, if that
>>date is in the table otherwise the tomorrow's date (based on the
>>current concept of 'tomorrow' on the computer on which the query is
>>run."
>>>
>>> Is this what you are asking?
>>>
>>> ---
>>> 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 [mailto:sqlite-users-
>>>>[hidden email]] On Behalf Of Csányi Pál
>>>>Sent: Thursday, 12 April, 2018 09:10
>>>>To: SQLite mailing list
>>>>Subject: Re: [sqlite] SELECT with CASE
>>>>
>>>>2018-04-12 17:08 GMT+02:00 Keith Medcalf <[hidden email]>:
>>>>>
>>>>> select TheDate from Dates where TheDate == date('now');
>>>>
>>>>Yes, but I want the CASE because if there is no such date in the
>>>>Dates
>>>>table which is equal to the date('now') then it should return the
>>>>date('now','+1 day').
>>>>_______________________________________________
>>>>sqlite-users mailing list
>>>>[hidden email]
>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
_______________________________________________
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: SELECT with CASE

R Smith-2
In reply to this post by csanyipal

> You're right.
> I am developing an Android app on App Inventor2.
> The app is in Hungarian language so the SQLite database contains
> tables and columns with Hungarian names.
>
> The whole schema is like this:
> CREATE TABLE Beiratkozottak(
>    az INTEGER PRIMARY KEY UNIQUE,
>    TanuloNeve TEXT NOT NULL,
>    EvFolyam INTEGER NOT NULL,
>    Tagozat TEXT NOT NULL
> );
> CREATE TABLE Hianyzok(
>    az INTEGER PRIMARY KEY,
>    HianyzoTanulo TEXT NOT NULL,
>    Orak INTEGER REFERENCES Orak(az) NOT NULL
> );
> CREATE TABLE JelenLevok(
>    az INTEGER PRIMARY KEY UNIQUE,
>    JelenLevoTanulo TEXT NOT NULL,
>    Orak INTEGER REFERENCES Orak(az) NOT NULL
> );
> CREATE TABLE Orak(
>    az INTEGER PRIMARY KEY UNIQUE,
>    Keltezes DATE DEFAULT NULL,
>    OrarendNapja TEXT DEFAULT NULL,
>    BlokkoraSzama TEXT DEFAULT NULL,
>    EvFolyam INTEGER DEFAULT NULL,
>    Tagozat TEXT DEFAULT NULL,
>    OraVazlat TEXT DEFAULT NULL,
>    OraNaplo TEXT DEFAULT NULL,
>    Emlekezteto TEXT DEFAULT NULL,
>    OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
>    Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
>    TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
> DEFAULT NULL,
>    KivetelesNapokOraRendjei INTEGER REFERENCES
> KivetelesNapokOraRendjei(az) DEFAULT NULL
> );
> CREATE TABLE OraRend(
>    az INTEGER PRIMARY KEY UNIQUE,
>    aHetNapja TEXT,
>    HanyadikIskolaOra INTEGER,
>    EvFolyam INTEGER,
>    Tagozat TEXT
> );
> CREATE TABLE TanSzombatokOraRendjei(
>    az INTEGER PRIMARY KEY UNIQUE,
>    Keltezes DATE DEFAULT NULL,
>    aHetNapja TEXT
> );
> CREATE TABLE KivetelesNapokOraRendjei(
>    az INTEGER PRIMARY KEY UNIQUE,
>    Keltezes DATE DEFAULT NULL,
>    aHetNapja TEXT
> );
>
> There are datas already in this database.
> The Orak table contains dates on which we have school, that is teaching.
> That mean not every day in the year is a school day.
> The OraRend table contains the timetable.
>
> So when I start the android app on my phone it should display the
> school day at that day, or, if on that day we have not a schoolday,
> then to display the next school day from the Orak table.

Ah, good we checked.

What guarantee is there then that "tomorrow" will be a valid school day
if today isn't?  What if today is Saturday, and we don't have school on
Saturday, then tomorrow is Sunday, which also might not be a school day?

To solve this puzzle, let's start by stating the problem in English:
"Give me the date for TODAY if TODAY is a valid school day, else give me
the date for the next possible valid school day."

Now let's translate that in terms of tables and records in a database:
"Give me the date for TODAY if any record exists in my table of
school-days for TODAY, else give me the first date (i.e. smallest date)
from a record that exists in the schooldays table AFTER (i.e.
later-than) today.

now let's say that in SQL:

SELECT MIN(TheDate)             -- get the smallest date
   FROM Orak                     -- from the table with School-days
  WHERE TheDate >= date('now')   -- where the school-day is later or
equal to today.
;

That should show the real date you need, in all cases, but assuming the
table has more available school days than today. (If nothing more
exists, nothing more can be shown).

Good luck!
Ryan

_______________________________________________
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: SELECT with CASE

Simon Slavin-3
On 12 Apr 2018, at 5:16pm, R Smith <[hidden email]> wrote:

> SELECT MIN(TheDate)             -- get the smallest date
>   FROM Orak                     -- from the table with School-days
>  WHERE TheDate >= date('now')   -- where the school-day is later or equal to today.
> ;

This reflects exactly the right structure for the data.  The following may be a little faster:

  SELECT TheDate
    FROM Orak
   WHERE TheDate >= date('now')
ORDER BY TheDate
   LIMIT 1

Both the above queries will perform far faster if there is an index on the "TheDate" column in Orak.  I'm not quite sure how your data is organised but this may perform another job too if it is a UNIQUE index.

Simon.
_______________________________________________
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: SELECT with CASE

Keith Medcalf

The Query Planner should decide in the case of the MIN containing query that the best solution is to traverse TheDate in order and return the first hit.  This may entail the creation of the necessary index if it does not exist and so the two plans should be more or less identical.

However, if used in a subquery, the inclusion of the LIMIT may preclude flattening whereas the MIN function version will not preclude flattening.  Since the most likely alternative to flattening is a co-routine it probably would not make much of a difference.

---
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 [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Thursday, 12 April, 2018 10:24
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>On 12 Apr 2018, at 5:16pm, R Smith <[hidden email]> wrote:
>
>> SELECT MIN(TheDate)             -- get the smallest date
>>   FROM Orak                     -- from the table with School-days
>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>equal to today.
>> ;
>
>This reflects exactly the right structure for the data.  The
>following may be a little faster:
>
>  SELECT TheDate
>    FROM Orak
>   WHERE TheDate >= date('now')
>ORDER BY TheDate
>   LIMIT 1
>
>Both the above queries will perform far faster if there is an index
>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>organised but this may perform another job too if it is a UNIQUE
>index.
>
>Simon.
>_______________________________________________
>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: SELECT with CASE

csanyipal
Thank you very much for the help and for the explanations.

Waw! It is so complicated at first! I hope I shall understand these soon.

Finally I decide to use this query:
SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
ORDER BY Keltezes LIMIT 1;

At last this works on my Gentoo linux system here, on my laptop.
I used datefudge to give a fake date to the sqlite3.
When I run the above mentioned query without 'localtime', it gives the
right date!
But, when I run the same query with 'localtime', then I get the localtime.

Because I think an Android operating system also uses localtime, I am
going to try out this query now.

2018-04-12 18:48 GMT+02:00 Keith Medcalf <[hidden email]>:

>
> The Query Planner should decide in the case of the MIN containing query that the best solution is to traverse TheDate in order and return the first hit.  This may entail the creation of the necessary index if it does not exist and so the two plans should be more or less identical.
>
> However, if used in a subquery, the inclusion of the LIMIT may preclude flattening whereas the MIN function version will not preclude flattening.  Since the most likely alternative to flattening is a co-routine it probably would not make much of a difference.
>
> ---
> 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 [mailto:sqlite-users-
>>[hidden email]] On Behalf Of Simon Slavin
>>Sent: Thursday, 12 April, 2018 10:24
>>To: SQLite mailing list
>>Subject: Re: [sqlite] SELECT with CASE
>>
>>On 12 Apr 2018, at 5:16pm, R Smith <[hidden email]> wrote:
>>
>>> SELECT MIN(TheDate)             -- get the smallest date
>>>   FROM Orak                     -- from the table with School-days
>>>  WHERE TheDate >= date('now')   -- where the school-day is later or
>>equal to today.
>>> ;
>>
>>This reflects exactly the right structure for the data.  The
>>following may be a little faster:
>>
>>  SELECT TheDate
>>    FROM Orak
>>   WHERE TheDate >= date('now')
>>ORDER BY TheDate
>>   LIMIT 1
>>
>>Both the above queries will perform far faster if there is an index
>>on the "TheDate" column in Orak.  I'm not quite sure how your data is
>>organised but this may perform another job too if it is a UNIQUE
>>index.
>>
>>Simon.
>>_______________________________________________
>>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
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: SELECT with CASE

csanyipal
2018-04-12 21:09 GMT+02:00 Csányi Pál <[hidden email]>:

> Thank you very much for the help and for the explanations.
>
> Waw! It is so complicated at first! I hope I shall understand these soon.
>
> Finally I decide to use this query:
> SELECT Keltezes FROM Orak WHERE Keltezes >= date('now','localtime')
> ORDER BY Keltezes LIMIT 1;
>
> At last this works on my Gentoo linux system here, on my laptop.
> I used datefudge to give a fake date to the sqlite3.
> When I run the above mentioned query without 'localtime', it gives the
> right date!
> But, when I run the same query with 'localtime', then I get the localtime.
>
> Because I think an Android operating system also uses localtime, I am
> going to try out this query now.

Well, it not works on my phone, altough I apply the 'localtime' in the query.
When I start the app it shows the TOMORROW date out there.
Can I ask you here what could be the solution?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
12