CODEWITHSUNDEEP
sqlsql-serverms-accessiif
CRIPRORA
CRIPRORA

Reputation: 43

Access query IIF statement to SQL Server query

Problem: I am trying to get the below code to work in SQL Server.

I wrote the code originally in an MS Access query, but now I need to use it in SQL Server. The problem I have is the IIF statements doesn't work in SQL Server.

I have tried using a Case statement but it ended up bogging up my SQL Server and never loaded any data. Is there any suggestions on how to convert an IIF statement to be used in SQL Server? Any feedback would be appreciated. Thanks!

Code written in MS Access query:

SELECT 
    Customer.CustomerID, 
    IIf([ExpenseCode].[SysCode] >= '1747', [Amount], 0) AS Trains
FROM   
    Customer 
LEFT JOIN 
    (Expense 
LEFT JOIN 
    ExpenseCode ON Expense.SysCode = ExpenseCode.SysCode) ON Customer.CustomerID = Expense.EntityID;

Attempted solution in SQL Server:

SELECT DISTINCT
    r.CustomerID,
    CASE
        WHEN t.SysCode = '1747'
           THEN e.Amount
        ELSE NULL
    END AS Trains
FROM
    ExpenseCode AS t, Expense AS e, Customer AS r

Upvotes: 0

Views: 3643

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

Your code should work in SQL Server. I would prefer writing it as:

SELECT c.CustomerID, 
       (CASE WHEN ec.SysCode >= '1747' THEN e.Amount ELSE 0 END) AS Trains
FROM Customer c LEFT JOIN 
     Expense e
     ON c.CustomerID = e.EntityID LEFT JOIN 
     ExpenseCode ec
     ON e.SysCode = ec.SysCode;

This guesses that Amount is coming from Expense.

The changes:

  • SQL Server (and no other databases!) require those awkward parentheses around multiple JOINs.
  • Table aliases make the query easier to write and to read.
  • CASE is the SQL standard for condition expressions, although SQL Server does "retrofit" IIF() for MS Access compatibility.
  • Qualify all column references!

Upvotes: 0

GMB
GMB

Reputation: 222582

IIF() is supported in SQL Server since version 2012. What you would need to change in your MS Access query is the syntax of the join.

Try:

SELECT 
    c.CustomerID, 
    IIf(ec.SysCode >= 1747, Amount, 0) AS Trains
FROM   
    Customer c
    LEFT JOIN Expense e ON c.CustomerID = e.EntityID
    LEFT JOIN ExpenseCode ec ON e.SysCode = ec.SysCode;

I also added table aliases to your query, since they make it more readable (please add the relevant prefix to column Amount). Finally, I considered that SysCode was a number, so I removed the surrounding quotes around the value for comparison (it it's indeed a string, you can put the quotes back).

If you are running SQL Server < 2012, here is the CASE sytanx:

SELECT 
    c.CustomerID, 
    CASE WHEN ec.SysCode >= 1747 THEN Amount ELSE 0 END AS Trains
FROM   
    Customer c
    LEFT JOIN Expense e ON c.CustomerID = e.EntityID
    LEFT JOIN ExpenseCode ec ON e.SysCode = ec.SysCode;

Upvotes: 2

Related Questions