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:sqlite-users-boun...@mailinglists.sqlite.org] 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 <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

Reply via email to