IIF statement in SQL Server 2005
Does IIF
statement exists in all version of SQL Serv开发者_C百科er ?
I have checked a tutorial on MSDN.
But when I tried to run this code on my machine
DECLARE @newDate datetime
SET @newDate = CONVERT(varchar, {fn NOW()}, 111)
SELECT IIF(@newDate > '2010/12/2', 'Greater', 'smaller')
But I am getting error of "Incorrect syntax near '>'."
Can someone provide me an example in SQL Server 2005 for the existence of the IIF
statement?
That IIF
statement only exists in MDX - the query language for SQL Server Analysis Services - the datawarehousing side of SQL Server.
Plain T-SQL does not have an IIF
statement.
The best you can do in T-SQL is use the CASE.... WHEN... THEN...
statement.
You're better off using a CASE expression:
DECLARE @newDate datetime
SET @newDate = CONVERT(varchar, {fn NOW()}, 111)
SELECT CASE WHEN @newDate > '20101202' THEN 'Greater' ELSE 'smaller' END
Please also note that I've switched your date literal to a safe format - '2010/12/2' could be interpreted by SQL server as either the 12th February or 2nd December.
IIF([Add DVP Month].DevelopmentMonth>[Add DVP Month].COVMONTHS,
1,
IIF([STATUS]<>'1',
1,
IIF([Add DVP Month].PLANTYPE = 'A' and [Add DVP Month].newUsedProgram = 'U' and [Add DVP Month].COVMONTHS = 60 and [Add DVP Month].COVMILES = 100000 and [Add DVP Month].postedDt >= #1/31/2010#,
IIF([Add DVP Month].postedDt >= #1/31/2012#,
[EPMthd.PCM2],
IIF([Add DVP Month].postedDt >= #1/31/2010#,
[EPMthd.PCM1],
[EPMthd.PCM0])
),
IIF([Add DVP Month].COVMONTHS = 999,[EPMthd.2],
IIF([Add DVP Month].postedDt >= #1/31/2012#, [EPMthd.2],
IIF([Add DVP Month].postedDt >= #1/31/2010#, [EPMthd.1],
IIF([Add DVP Month].postedDt >= #1/31/2008#,
IIF([EPMthd.0] is null,
[EPMthd.8],
[EPMthd.0]
),
IIF([Add DVP Month].postedDt < #1/31/2008#,
IIF([EPMthd.8] is null,
IIF([Add DVP Month].COVMONTHS = 0,0,
[Add DVP Month].DevelopmentMonth/[Add DVP Month].COVMONTHS
),
[EPMthd.8]
),
IIF([Add DVP Month].COVMONTHS = 0,
0,
[Add DVP Month].DevelopmentMonth/[Add DVP Month].COVMONTHS
)
)
))))))
) AS [EP%]
精彩评论