Case expression failing

Question

Trying to run the SQL using a case expression but seems there is an syntax error

SELECT
    MAX(date) AS maxdate,
    MIN(date) AS mindate, 
    id
WHERE 
    (iSkid = 1)

CASE 
   WHEN PageList = 0
      THEN
         AND (startdate <= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
         AND (enddate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
END
GROUP BY 
    id, maxdate, mindate

Errors I get:

Msg 156, Level 15, State 1, Line 95
Incorrect syntax near the keyword 'CASE'.

tried this way too

select max(date) as maxdate,min(date) as mindate, id



CASE WHEN
        PageList = 0
    THEN
        WHERE (iSkid = 1) AND (startdate <= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
        AND (enddate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
ELSE 
WHERE (iSkid = 1)
    END
GROUP BY 
    id,maxdate,mindate  

    Msg 156, Level 15, State 1, Line 95
Incorrect syntax near the keyword 'CASE'.

but something is messed up, and not sure what. any clue?

In the second example, you are missing a comma before the `case`. You have a bunch of other problems with the query. I would recommend that you delete this question. Then ask a new question, explain the logic you want to implement and provide sample data and desired results. — Gordon Linoff, Nov 13 '19 at 17:40
Possible duplicate of ["CASE" statement within "WHERE" clause in SQL Server 2008](https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008) — Amira Bedhiafi, Nov 13 '19 at 17:40
better to change your question as you have not written your query in proper way. so explain the problem and sample data with screenshot or so. — UsmanMirza, Nov 13 '19 at 17:50
you've mixed CASE and WHERE in an incorrect way, CASE can be used to select a value to apply to WHERE, but not in that way — Cato, Nov 13 '19 at 17:54
Since you need to group by id, the group by should only be GROUP BY maxdate, mindate. — Zorkolot, Nov 13 '19 at 18:08

score 0 · Answer 1 · answered Nov 13 '19 at 17:51

You cannot use CASE to conditionally insert clauses or return partial expressions.

Your first example is the logical equivalent of something like 1 IF(something, + 2, - 1), which is fairly obviously invalid, and not something you'd probably ever right. The more common, valid expression of that would be 1 + IF(something, 2, -1).

CASE returns values, not expressions (neither full nor partial ones) and not clauses. AND something is a partial expression, but something should evaluate to a value, as would something AND somethingelse. So, while CASE WHEN x THEN AND something .... doesn't make sense, something like AND CASE WHEN x THEN something ... does. Similarly, CASE WHEN x THEN WHERE somthing .... is incorrectly formed; WHERE CASE WHEN x THEN something .... is the correct form.

tldr: Your conditions* in first one were close to correct; you just needed the first AND before the CASE.... and to determine the truth value that the CASE should return when PageList = 0 is false. (* a GROUP BY including aggregate results is nonsense too.)

score 0 · Answer 2 · answered Nov 13 '19 at 18:02

0

I think your intention is this:

WHERE 
    (iSkid = 1)
    OR
         (PageList = 0
         AND (startdate <= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
         AND (enddate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)) 
         )

answered Nov 13 '19 at 18:02

KeithL

5,348
3
19
25

Case expression failing

2 Answers2