January 23, 2012 at 3:09 pm
I have an IF statement that is used in access:
Current: Sum(IIf([days old]<=0,[Balance Remaining],0))
I'd like to convert this statement to SQL Server. I tried using CASE to replicate its effects, but I am getting syntax errors.
SUM(CASE
WHEN [days old] <= 0 THEN [balance remaining]
ELSE 0
END) AS Current
What part of the function is causing the error? Is there any other way to achieve the results that the IF statement gives?
January 23, 2012 at 3:16 pm
Looks fine to me:
use tempdb
CREATE TABLE #temp (id int identity(1,1), value money)
INSERT INTO #temp
SELECT 2.50
UNION ALL
SELECT 4.00
UNION ALL
SELECT 500.00
SELECT SUM(CASE
WHEN id <= 2 THEN value
ELSE 0
END) AS sum_value
FROM #temp
DROP TABLE #temp
This runs fine.
Jared
CE - Microsoft
January 23, 2012 at 3:36 pm
Did some testing. I am able to run
sum(CASE
WHEN [days old] <= 0 THEN [balance remaining]
ELSE 0
END)
but not
sum(CASE
WHEN [days old] <= 0 THEN [balance remaining]
ELSE 0
END) AS Current
Anyone know why "AS Current" would generate an error?
January 23, 2012 at 3:39 pm
As you can see, my query works. In order for us to help you, please post the ddl of the table you are running this on, some sample data, and the complete query, not just the snippet.
Jared
CE - Microsoft
January 23, 2012 at 3:41 pm
Side note... Current is a reserved SQL keyword. If you must use it, put it in []. However, I suggest picking something else.
Jared
CE - Microsoft
January 23, 2012 at 6:08 pm
The IIF logical function has been added to SQL Server 2012 to ease migration for Access users.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply