Syntax Error when Using Case Statement

Question

I am using CASE statement to print various seasons based on select statement value

SELECT 
    (CASE WHEN (SELECT po.startdate 
                FROM TABLE1 po
                JOIN TABLE2 a ON po.paramid = a.paramid
                WHERE po.paramid = 60)
             THEN 
                (CASE WHEN DATEPART(month, po.STARTDATE) < 3 
                         THEN 'Winter'
                      WHEN DATEPART(month, po.STARTDATE) = 3 
                         THEN 
                            CASE WHEN DATEPART(day, po.STARTDATE) < 01 
                                    THEN 'Winter' 
                                    ELSE 'Spring' 
                            END
                     WHEN DATEPART(month, po.STARTDATE) < 6 
                        THEN 'Spring'
                     WHEN DATEPART(month, po.STARTDATE) = 6 
                        THEN
                           CASE WHEN DATEPART(day, po.STARTDATE) < 21 
                                   THEN 'Spring' 
                                   ELSE 'Summer' 
                           END
                    WHEN DATEPART(month, po.STARTDATE) < 9 
                       THEN 'Summer'
                    WHEN DATEPART(month, po.STARTDATE) = 9 
                       THEN 
                          CASE WHEN DATEPART(day, po.STARTDATE) < 21 
                                  THEN 'Summer' 
                                  ELSE 'Autumn' 
                          END
                   WHEN DATEPART(month, po.STARTDATE) < 12 
                      THEN 'Autumn'
                   WHEN DATEPART(month, po.STARTDATE) = 12 
                      THEN
                         CASE WHEN DATEPART(day, po.STARTDATE) < 1 
                                 THEN 'Autumn' 
                                 ELSE 'Winter' 
                         END) 
    END) 
FROM 
    tABLE1 PO

Error:

Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

Please can someone suggest what's wrong with this code?

Answer 1

It looks like your error has to do with the syntax and formatting used which looks to be malformed. Rather than correct the error, I'd tackle the issue fresh from scratch.

With that in mind, what you look to be trying to do is return the season based on specific date ranges. Others may have an alternate way but one approach is like so;

SELECT (CASE 
        WHEN d.DateMonth < 3 OR d.DateMonth = 12
            THEN 'Winter'
        WHEN d.DateMonth >=3 AND (d.DateMonth < 6
                                      OR (d.DateMonth = 6 AND d.DateDay < 21))
            THEN 'Spring'
        WHEN (d.DateMonth = 6 AND d.DateDay >= 21)
             AND (d.DateMonth < 9
                    OR (d.DateMonth = 9 AND d.DateDay < 21))
            THEN 'Summer'
        ELSE 'Autumn'
    END) AS Season
FROM TABLE1 po
JOIN TABLE2 a
  ON po.paramid=a.paramid
-- select the date components we want rather than calling
-- DATEPART each time we need a date segment
OUTER APPLY (SELECT DATEPART(MONTH, po.StartDate) AS DateMonth,
                    DATEPART(DAY, po.StartDate) AS DateDay) d
WHERE po.paramid=60

Answer 2

I cannot imagine that a query of this form actually does anything useful. However, your specific problem is right at the beginning:

SELECT (case when (select po.startdate
                   from TABLE1 po join
                        TABLE2 a
                        on po.paramid = a.paramid
                   where po.paramid = 60
                  )
             then . . .

The case is expecting a boolean condition. It is getting a date. Hence, an error. An easy way to fix this error is:

SELECT (case when exists (select po.startdate
                          from TABLE1 po join
                               TABLE2 a
                               on po.paramid = a.paramid
                          where po.paramid = 60
                         )
             then . . .

I don't know if that is the intended logic. And, I'm almost certain that if the query ran it would not do what you want (the alias po is suspiciously defined twice).

I would advise you to ask another question. Describe the logic you want to implement. Provide sample data and desired results. I suspect that a subquery is not needed at all for what you want to do.