Im trying to use case to vary the value im checking in a where clause but I'm getting the error:
incorrect syntax near the keyword 'CASE'
SQL Server 2005
where ((CASE when adsl_order_id like '95037%'
then select '000000'+substring(adsl_order_id,6,6)
ELSE select adsl_order_id
not in (select mwebID from tmp_csv_dawis_bruger0105)
Here is one way to include a case statement in a Where clause:
SELECT * FROM sometable WHERE 1 = CASE WHEN somecondition THEN 1 WHEN someothercondition THEN 2 ELSE ... END
You could 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)
A correlated subquery is one possibility:
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 )
You have one too many opening parentheses before the CASE expression.