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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
