TSQL- Using CASE in WHERE clause with a < or =

问题:

I am trying to select the IDs based on period and type of account.Each account type would have a different period. I tried the following code but it didnt like the < operator or any other operators. I cannot use a temp table due to system limitation. There are at least 6 types of accounts. I searched for different solutions but I didnt find anything . Any help is appreciated. Thank you

DECLARE @Inputperiod as INT 
DECLARE @Start as INT
DECLARE @Enddate as INT

    SET @Inputperdiod ='2009';
    SET @Start=@Inputperiod-1;
    SET @Enddate=@Inputperiod +1;

        SELECT ID, Period, Type 
        FROM
        TABLE1

        WHERE 

        Period= CASE

        WHEN Type='ASSET' THEN Period < @inputperiod 
        WHEN Type='Liability' THEN Period BETWEEN @start AND @enddate
        END

回答1:

You're misunderstanding the case statement. What you have in your THEN clause is what is returned by the CASE statement (like an if... then... statement). The way you have it written, it returns a boolean.

Try this instead...

SELECT ID, Period, Type
FROM Table1
WHERE Type='ASSET' AND Period < @inputperiod
UNION
SELECT ID, Period, Type
FROM Table1
WHERE Type='Liability' AND Period BETWEEN @start AND @enddate 

回答2:

You can't use CASE to swap out arbitrary bits of the query. You would need to use

   SELECT ID, Period, Type 
   FROM TABLE1
   WHERE (Type='ASSET' AND Period < @inputperiod ) 
    OR    (Type='Liability' AND Period BETWEEN @start AND @enddate)