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