Using IIF function in OPENQUERY

Question

Declare @OPENQUERY nvarchar(500), @TSQL nvarchar(max), @LinkedServer nvarchar(20), @PickedDate varchar(8)

Set @LinkedServer = 'LinkedServerName'
Set @OPENQUERY = 'Select * From Openquery('+ @LinkedServer + ','''
Set @TSQL = 'SELECT sum(iif(left(code,1)=''''C'''', 1, 0) As ActiveCases
            From cases
            Where cases.date_opened = ''''' + @vcPickedDate + '''''
            '')'
Exec (@Openquery+@TSQL)

When I ran this query, it returned message

"[Sybase][ODBC Driver][SQL Anywhere]Syntax error near '=' on line 1". An error occurred while preparing the query "SELECT Sum(IIf(Left(code,1)='C',1,0)) AS Cases FROM cases WHERE cases.date_opened = '20150511' " for execution against OLE DB provider "MSDASQL" for linked server "LinkedServerName".

I figured this has something to do with openquery. It works fine if I simply run the following query without using openquery:

SELECT sum(iif(left(code,1)=''''C'''', 1, 0) As ActiveCases
                From cases
                Where cases.date_opened = ''''' + @vcPickedDate + '''''

Answer 1

I think the version of SQL Server you are using is under 2012 and IIF is not supported for earlier versions.

You can use CASE instead.

SELECT Sum(CASE Left(matcode,1) WHEN 'R' THEN 1 ELSE 0 END) AS Cases
FROM cases
WHERE cases.date_opened = '20150511'

Edit

Based on Zerubbabel comment, he is using SQL Server 2014 and the problem was for using MSDASQL which is quite old and it doesn't support IIF function. So the problem has nothing to do about IIF in SQL Server but in OPENQUERY.

SELECT Sum(CASE Left(code,1) WHEN ''''C'''' THEN 1 ELSE 0 END) AS Cases
FROM cases
WHERE cases.date_opened = '20150511'