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):

    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!

sqlite-users mailing list

Reply via email to