SELECT with CASE

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

SELECT with CASE

csanyipal
Hi,

I have a small database:
DatesOfYear.db

with only one table:

CREATE TABLE Dates(id integer PRIMARY KEY UNIQUE, TheDate date NOT NULL);

I insert into the table some datas with:

INSERT INTO Dates VALUES(1,'2018-04-01');
INSERT INTO Dates VALUES(2,'2018-04-02');
INSERT INTO Dates VALUES(3,'2018-04-03');
INSERT INTO Dates VALUES(4,'2018-04-04');
INSERT INTO Dates VALUES(5,'2018-04-05');
INSERT INTO Dates VALUES(6,'2018-04-06');
INSERT INTO Dates VALUES(7,'2018-04-07');
INSERT INTO Dates VALUES(8,'2018-04-08');
INSERT INTO Dates VALUES(9,'2018-04-09');
INSERT INTO Dates VALUES(10,'2018-04-10');
INSERT INTO Dates VALUES(11,'2018-04-11');
INSERT INTO Dates VALUES(12,'2018-04-12');
INSERT INTO Dates VALUES(13,'2018-04-13');
INSERT INTO Dates VALUES(14,'2018-04-14');
INSERT INTO Dates VALUES(15,'2018-04-15');

I want the following by using SELECT and CASE:

to select the date:
 if TheDate is = with the current date then select that TheDate
 else to select only once the next date  from Dates table.

I tried with this query:
SELECT CASE TheDate = date('now') WHEN TheDate THEN TheDate ELSE
date('now','+1 day') END TheDate FROM Dates;

and the output is this:
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13

but I expect - because to day is 2018-04-12 this:
2018-04-12

What am I doing wrong?
NOTE! The dates are in Hungarian.

--
Best, Pali
_______________________________________________
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

Simon Slavin-3
On 12 Apr 2018, at 1:25pm, Csányi Pál <[hidden email]> wrote:

> SELECT CASE TheDate = date('now') WHEN TheDate ...

I don't think that's what you wanted.  Perhaps

SELECT CASE TheDate WHEN date('now') ...

But you should test the output of "date('now')" to make sure it is in the format you want.

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

csanyipal
Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin <[hidden email]>:
> On 12 Apr 2018, at 1:25pm, Csányi Pál <[hidden email]> wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

--
Best, Pali
_______________________________________________
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

Peter da Silva
You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what you're getting, no?

On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" <[hidden email] on behalf of [hidden email]> wrote:

    Hi Simon,
   
    2018-04-12 14:32 GMT+02:00 Simon Slavin <[hidden email]>:
    > On 12 Apr 2018, at 1:25pm, Csányi Pál <[hidden email]> wrote:
    >
    >> SELECT CASE TheDate = date('now') WHEN TheDate ...
    >
    > I don't think that's what you wanted.  Perhaps
    >
    > SELECT CASE TheDate WHEN date('now') ...
    >
    > But you should test the output of "date('now')" to make sure it is in the format you want.
   
    The output of "date('now') is in format I want:
   
    SELECT date('now');
    2018-04-12
   
    I tried this:
   
    SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
    day') END TheDate FROM Dates;
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-13
    2018-04-12
    2018-04-13
    2018-04-13
    2018-04-13
   
    but I expect the output like this:
    2018-04-12
   
    because today date is:
    2018-04-12
   
    What am I missing here?
   
    --
    Best, Pali
    _______________________________________________
    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
2018-04-12 16:51 GMT+02:00 Peter Da Silva <[hidden email]>:
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
_______________________________________________
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

Peter da Silva
One of the lines of the output does indeed have '2018-04-12' as expected.

On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" <[hidden email] on behalf of [hidden email]> wrote:

    2018-04-12 16:51 GMT+02:00 Peter Da Silva <[hidden email]>:
    > You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what you're getting, no?
   
    Yes, indeed.
    But I thought the first part would be done:
    CASE TheDate WHEN date('now') THEN TheDate
   
    that is, if the TheDate is = date('now') THEN
    it should select that TheDate which is equal to the current date:
    date('now'), no?
    _______________________________________________
    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
2018-04-12 17:00 GMT+02:00 Peter Da Silva <[hidden email]>:
> One of the lines of the output does indeed have '2018-04-12' as expected.

Indeed, I did not notice.
Then how can I get only that date from the Dates table - which is
equal to the current date?
_______________________________________________
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

Keith Medcalf

select TheDate from Dates where TheDate == date('now');

---
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:06
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>2018-04-12 17:00 GMT+02:00 Peter Da Silva
><[hidden email]>:
>> One of the lines of the output does indeed have '2018-04-12' as
>expected.
>
>Indeed, I did not notice.
>Then how can I get only that date from the Dates table - which is
>equal to the current date?
>_______________________________________________
>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
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-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: SELECT with CASE

Richard Hipp-3
On 4/12/18, Csányi Pál <[hidden email]> wrote:
> 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').

... COALESCE((SELECT thedate FROM dates WHERE thedate==date('now')),
date('now','+1 day'))

--
D. Richard Hipp
[hidden email]
_______________________________________________
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
In reply to this post by csanyipal

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-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
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-users
>
>
>
> _______________________________________________
> 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

Keith Medcalf

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-
>users
>>
>>
>>
>> _______________________________________________
>> 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



_______________________________________________
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!

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
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: SELECT with CASE

R Smith-2
In reply to this post by csanyipal
On 2018/04/12 5:20 PM, Csányi Pál wrote:
> Yes, this is what I am asking.
>
> 2018-04-12 17:17 GMT+02:00 Keith Medcalf <[hidden email]>:
>> 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?

Ok, now that we know what you are asking, there are some ways of doing
it easily, of which Richard's way will work perfectly.

But, some more information will be useful:
- Can there be dates later than today in the table or not?
- Can there be multiple dates for today in the table, or just the one?
- Do you need to run this query often, or is it used simply to determine
the next date available for another query (insert perhaps)?

I'm asking because I feel like the query you are asking for is achieving
something as part of a larger query or group of functions that may all
be made simpler. I could of course be wrong, but if you'd like to find
out, post the whole schema and method you are trying to make and we
could suggest what might work the fastest/easiest/best - or we might at
least confirm that you are already doing it the best way.

Cheers!
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

Keith Medcalf
In reply to this post by Keith Medcalf

Assuming, of course, that all the dates are in UT1 (UTC/GMT/Zulu) and not the computers' (running the query) current concept of 'local wall clock time'.  

If your table of Dates are not UT1 then you need to add the 'localtime' modifier to the datetime function calls and pray that the computer has the correct "rules" for the translation from UT1/GMT/Zulu to your desired 'localtime'.

---
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 Keith Medcalf
>Sent: Thursday, 12 April, 2018 09:26
>To: SQLite mailing list
>Subject: Re: [sqlite] SELECT with CASE
>
>
>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-
>>users
>>>
>>>
>>>
>>> _______________________________________________
>>> 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
>
>
>
>_______________________________________________
>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
In reply to this post by R Smith-2
Hi Ryan,

2018-04-12 17:36 GMT+02:00 R Smith <[hidden email]>:

> On 2018/04/12 5:20 PM, Csányi Pál wrote:
>>
>> Yes, this is what I am asking.
>>
>> 2018-04-12 17:17 GMT+02:00 Keith Medcalf <[hidden email]>:
>>>
>>> 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?
>
>
> Ok, now that we know what you are asking, there are some ways of doing it
> easily, of which Richard's way will work perfectly.
>
> But, some more information will be useful:
> - Can there be dates later than today in the table or not?
> - Can there be multiple dates for today in the table, or just the one?
> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
>
> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.
>
> Cheers!
> Ryan

> - Can there be dates later than today in the table or not?
Yes, it can.

> - Can there be multiple dates for today in the table, or just the one?
Yes, it can.

> - Do you need to run this query often, or is it used simply to determine the
> next date available for another query (insert perhaps)?
It is used simply to determine the next date available for another query.

> I'm asking because I feel like the query you are asking for is achieving
> something as part of a larger query or group of functions that may all be
> made simpler. I could of course be wrong, but if you'd like to find out,
> post the whole schema and method you are trying to make and we could suggest
> what might work the fastest/easiest/best - or we might at least confirm that
> you are already doing it the best way.

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.
_______________________________________________
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

R Smith-2
In reply to this post by csanyipal
On 2018/04/12 5:35 PM, Csányi Pál wrote:
> 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?

The CASE expression modifies a single line, the WHERE clause restricts
the selection to the lines that qualify.

So if you decide to do it in a CASE expression, your case expression
worked just fine.

Your sql was (expanded a little for legibility):

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


Which, when translated to plain English says:

a. For every record in table"Dates", show me a value called "TheDate"
which is decided upon as follows:
   b. When the value in column "TheDate" is equal to today's date (for
what my current computer thinks is "today" locally - let's call this
TODAY), then simply put THAT TheDate value,
   c. Else put tomorrow's date (let's call this TOMORROW).

Now some things you can see from this:
- Because of a. - you will always see ALL rows listed.
- In b. the selection is superfluous. The only value that can ever be
given for TheDate is TODAY.   [If TheDate==TODAY, then show TheDate
(i.e. TODAY)]
- in c. the selection can only ever be TOMORROW, nothing else. For you
specify that if NOT (TheDate == TODAY) - i.e. the ELSE, then show
TOMORROW. Always.

This means the only two possible dates that can be the result of your
CASE statement is either TODAY, or TOMORROW. Nothing else. And indeed,
in the output that you sent, it is clearly the case, you have lots of
lines showing TOMORROW (obviously for the entries in your table where
the "TheDate" column was NOT equal to TODAY), and one line showing TODAY
(obviously for the one entry where the "TheDate" column was indeed equal
to TODAY).

What you then later asked is that you do not wish to see any of this,
you want to know whether there exists a date such as TODAY in the table
at all... If so, you want one single answer showing that date (i.e.
TODAY), else you would like the one single answer to say TOMORROW. This
absolutely /HAS/ to be filtered out using a WHERE clause. There is no
way CASE can limit the shown rows - it only selects based upon a value
in the current row.

Does that answer the CASE question? If not, feel free to ask again with
maybe examples of how you expect it to work, which will help us to know
what misconception to assist with.

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

Peter da Silva
In reply to this post by csanyipal
Ah, so if there's two days in a row that aren't school days, you need to be able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" <[hidden email] on behalf of [hidden email]> wrote:
    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.
    _______________________________________________
    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

Peter da Silva
Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= date('now');

On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" <[hidden email] on behalf of [hidden email]> wrote:

    Ah, so if there's two days in a row that aren't school days, you need to be able to select a day two or more days in the future.
   
    On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" <[hidden email] on behalf of [hidden email]> wrote:
        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.
        _______________________________________________
        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
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12