Bouje Bouje - 6 months ago 51
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
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 = 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

Use

SELECT case when dbo.GL_Register.Credit_Or_Debit = 'C' 
            then 'true' 
            else 'false'
       end
from dbo.GL_Register