SQLTeam.com | Weblogs | Forums

CASE statement in WHERE clause - error

#1

I need to use a CASE expression in a WHERE clause but am getting an error. The code as follows -

...
WHERE (CASE WHEN month(getdate()) > 1 THEN
left([table_1].[object_1], 4) = year(getdate()) -- getting error on this equals sign
ELSE
left([table_1].[object_1], 4) = year(getdate())-1
END
)

The code isn't an exact copy but it's the error on the first '=' that is always flagged ('incorrect syntax')
Thanks

#2 
WHERE 
     (CASE WHEN month(getdate()) > 1 THEN   CAST(left([table_1].[object_1], 4) AS INT)
                  ELSE  CAST(left([table_1].[object_1], 4) AS INT) +1
       END
    ) = year(getdate())
#3

Thanks. There was a bit more to it that I thought if left off would simplify it. Basically if the month(getdate()) is greater than 1 then I need the current year (of object) and any prior months (of same) i.e.

201702, 201701...

CASE WHEN month(getdate()) >1 THEN left([table_1].[object_1], 4) = year(getdate()) AND right([table_1].[object_1], 2) < month(getdate())
ELSE ...

Obviously syntax is again wrong. Thanks for any help here.

#4 
WHERE 
     (CASE WHEN month(getdate()) > 1 THEN   CAST(left([table_1].[object_1], 4) AS INT)
                  ELSE  CAST(left([table_1].[object_1], 4) AS INT) +1
       END
     ) = year(getdate())
	AND
	(CASE WHEN month(getdate()) > 1 THEN  right([table_1].[object_1], 2) 
	ELSE ...
        END
    ) < month(getdate())

To be easy: generate the first part of your CASE like a column , inside a CTE and then complete the WHERE

CTE AS (
 SELECT ... 
	,CASE WHEN month(getdate()) > 1 THEN   CAST(left([table_1].[object_1], 4) AS INT)
                  ELSE  CAST(left([table_1].[object_1], 4) AS INT) +1
         END AS myYear
	,CASE WHEN month(getdate()) > 1 THEN   right([table_1].[object_1], 2)
                  ELSE  right([table_1].[object_1], 2)
         END   myMonth
 FROM
	...
)
SELECT ... 
FROM 
	CTE
WHERE
	myYear = year(getdate()) /* this is not SARGable*/
	AND
	myMonth < month(getdate())
#5

Thanks stepson. I've got it working but finally need to cater for Jan going back into the previous year as follows. There seems too much repetition though and ideally I need to shorten the 2 parts (where month(getdate) > 1 and where month(getdate) = 1).
(s_num and i_num are now named.). Thanks.

WHERE
(
case when MONTH(getdate()) > 1 then
cast(LEFT(s_num, 4) AS INT) end = YEAR(getdate())
AND
(case when MONTH(getdate()) > 1 then
CAST(right(s_num, 2) AS INT) end) < month(getdate())
)
OR
(
case when MONTH(getdate()) > 1 then
cast(LEFT(i_num, 4) AS INT) end = YEAR(getdate())
AND
(case when MONTH(getdate()) > 1 then
CAST(right(i_num, 2) AS INT) end) < month(getdate())
)

or

(case when MONTH(getdate()) = 1 then
cast(LEFT(s_num, 4) AS INT) end = YEAR(getdate())-1
AND
(case when MONTH(getdate()) = 1 then
CAST(right(s_num, 2) AS INT) end) <= 12
)
OR
(
case when MONTH(getdate()) = 1 then
cast(LEFT(i_num, 4) AS INT) end = YEAR(getdate())-1
AND
(case when MONTH(getdate()) = 1 then
CAST(right(i_num, 2) AS INT) end) <= 12
)

#6

Maybe this instead?:

WHERE	
(MONTH(GETDATE()) > 1 AND (s_num < CONVERT(varchar(6), GETDATE(), 112) OR 
                          (i_num < CONVERT(varchar(6), GETDATE(), 112))))
OR
(MONTH(GETDATE()) = 1 AND (s_num < CONVERT(varchar(6), DATEADD(YEAR, -1, GETDATE()), 112) OR 
                          (i_num < CONVERT(varchar(6), DATEADD(YEAR, -1, GETDATE()), 112))))