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-
>boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com>:
>>
>> 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-
>>>boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com>:
>>>>
>>>> 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-
>>>>>boun...@mailinglists.sqlite.org] 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 <kmedc...@dessus.com>:
>>>>>>
>>>>>> 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
>>>>>sqlite-users@mailinglists.sqlite.org
>>>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
