Using CASE in T-SQL in a where clause?

Question

I am trying to use case to change the value of im checking in the where clause, but getting an error:

incorrect syntax next to the keyword "CASE"

SQL Server 2005

select * from table where ((CASE when adsl_order_id like '95037%' then select '000000'+substring(adsl_order_id,6,6) ELSE select adsl_order_id END) not in (select mwebID from tmp_csv_dawis_bruger0105) 

Answer 1

Here is one way to include the case statement in the Where clause:

 SELECT * FROM sometable WHERE 1 = CASE WHEN somecondition THEN 1 WHEN someothercondition THEN 2 ELSE ... END 

Answer 2

You can try

 SELECT * FROM table WHERE (SELECT CASE WHEN adsl_order_id LIKE '95037%' THEN '000000' + SUBSTRING(adsl_order_id, 6, 6) ELSE adsl_order_id END) NOT IN (select mwebID from tmp_csv_dawis_bruger0105) 

Answer 3

Correlated subquery is one of the possibilities:

 select * from mytable where not exists ( select * from tmp_csv_dawis_bruger0105 where mwebID = CASE when mytable.adsl_order_id like '95037%' then '000000' + substring(mytable.adsl_order_id,6,6) ELSE mytable.adsl_order_id END ) 

Answer 4

Put it in a SELECT clause ...

 select *, (CASE when adsl_order_id like '95037%' then '000000'+substring(adsl_order_id,6,6) ELSE adsl_order_id END) AS Id from table where not in (select mwebID from tmp_csv_dawis_bruger0105) 

In addition, you do not need to “SELECT” the CASE result.

Answer 5

You have too many opening parentheses before the CASE expression.