Bouje Bouje - 1 year ago 162
SQL Question

IIF troubles with SQL Server 2012

I'm trying to use IIF in SQL Server 2012 Management studio (Connected to SQL Server 2012). I can produce what I need in Access but the syntax in SQL Server is tripping me up. Even something simple like

SELECT IIF(dbo.GL_Register.Credit_Or_Debit = C,'true','false')
from dbo.GL_Register

SHould work but brings back the following error: "Error in list function arguments: '=' not recognized. Unable to parse query text.

This is so easy to do in Access. What am I doing wrong? Thanks.

updated, got it to halfway work using "like" instead of equals. Then it says "FROM" is a problem:

SELECT dbo.GL_Account.Account_Code, dbo.GL_Register.Date,
SUM(IIF(dbo.GL_Register.Credit_Or_Debit like 'C',-
dbo.GL_Register.Amount,dbo.GL_Register.Amount) AS Expr1
dbo.GL_Account ON dbo.GL_Register.Account_Id = dbo.GL_Account.Account_Id
GROUP BY dbo.GL_Account.Account_Code, dbo.GL_Register.Date
HAVING (dbo.GL_Account.Account_Code = N'11000')

The Access SQL that works like a charm is:

SELECT dbo_GL_Account.Account_Code, dbo_GL_Register.Date,
Sum(IIf([Credit_Or_Debit]="C",-[Amount],[Amount])) AS Amount2
FROM dbo_GL_Register INNER JOIN dbo_GL_Account ON dbo_GL_Register.Account_Id
= dbo_GL_Account.Account_Id
GROUP BY dbo_GL_Account.Account_Code, dbo_GL_Register.Date
HAVING (((dbo_GL_Account.Account_Code)="11000"));

Answer Source


SELECT case when dbo.GL_Register.Credit_Or_Debit = 'C' 
            then 'true' 
            else 'false'
from dbo.GL_Register
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download