Using IIF function in OPENQUERY

1
votes
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 + '''''
3
sql-serverdynamic-sqllinked-serveriifopenquery
I think the issue is with your use of escaped single-quotes. - Dai
Have you tried using the native client OLEDB provider - MSDASQL is old (SQLNCLI12) ? - ErikEJ
I have to use MSDASQL, OLEDB Provider for ODBC drivers. But your comment helps me solve the problem. Thanks Erik. - Weihui Guo

3 Answers

2
votes

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'
0
votes

I think this query will help you :

Set @TSQL = 'SELECT sum(iif(left(code,1)=''''C'''', 1, 0) As ActiveCases From cases
        Where cases.date_opened = '+''''''+@PickedDate+''''''+''')';
0
votes

It's not the SQL server I am using that is old, I use 2014. It's the linked server provider MSDASQL, which is old. So sqluser's answer is close and ErikEJ's comment is helpful too. And I do have to use CASE instead of IIF. The query below works. NOTE that this question is not simply about issue using IIF in SQL server, but using it in an openquery.

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