sql server - Using IIF function in OPENQUERY -

declare @openquery nvarchar(500), @tsql nvarchar(max), @linkedserver nvarchar(20), @pickeddate varchar(8)  set @linkedserver = 'linkedservername' set @openquery = 'select * openquery('+ @linkedserver + ',''' set @tsql = 'select sum(iif(left(code,1)=''''c'''', 1, 0) activecases             cases             cases.date_opened = ''''' + @vcpickeddate + '''''             '')' exec (@openquery+@tsql) 

when ran query, returned message

"[sybase][odbc driver][sql anywhere]syntax error near '=' on line 1". error occurred while preparing query "select sum(iif(left(code,1)='c',1,0)) cases cases cases.date_opened = '20150511' " execution against ole db provider "msdasql" linked server "linkedservername".

i figured has openquery. works fine if run following query without using openquery:

select sum(iif(left(code,1)=''''c'''', 1, 0) activecases                 cases                 cases.date_opened = ''''' + @vcpickeddate + ''''' 

i think version of sql server using under 2012 , iif not supported earlier versions.

you can use case instead.

select sum(case left(matcode,1) when 'r' 1 else 0 end) cases cases cases.date_opened = '20150511' 

edit

based on zerubbabel comment, using sql server 2014 , problem using msdasql quite old , doesn't support iif function. problem has nothing iif in sql server in openquery.

select sum(case left(code,1) when ''''c'''' 1 else 0 end) cases cases cases.date_opened = '20150511'