June 23, 2016 at 11:00 am
I would like to ask for help reviewing the conversion I just did taking a piece of vb6 code and turn it to sql syntax.
VB6
IIF([Type] = 1, 26, IIF([Type] = 2, 27, 28))
SQL
, CASE
WHEN [Type] = 1 THEN 26
ELSE
CASE
WHEN [Type] = 2 THEN 27
ELSE 28
END
END
June 23, 2016 at 11:07 am
, CASE
WHEN [Type] = 1 THEN 26
WHEN [Type] = 2 THEN 27
ELSE 28
END
This should work. The IIF only allows two possible cases, but the CASE can support more cases than you are ever likely to need.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 23, 2016 at 11:10 am
Thank you Drew.
June 23, 2016 at 11:16 am
itortu (6/23/2016)
I would like to ask for help reviewing the conversion I just did taking a piece of vb6 code and turn it to sql syntax.VB6
[code-"sql"]
IIF([Type] = 1, 26, IIF([Type] = 2, 27, 28))
[/code]
SQL
, CASE
WHEN [Type] = 1 THEN 26
ELSE
CASE
WHEN [Type] = 2 THEN 27
ELSE 28
END
END
Alternatively, this version actually works too:
iif(Type = 1, 26,iif(Type = 2,27,28))
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 23, 2016 at 11:21 am
That is correct too. I am aware now that SQL 2012, which is the version I am using, supports IIF expression.
June 27, 2016 at 8:19 am
Drew points out that the CASE statement allows for more than 2 conditions which is a good thing to have in place for future expansion.
I also believe that SQL evaluates IIF into a CASE statement anyway so why not give the server a break and write it as a CASE statement in the first place?
If you can't tell I'm not a fan of IIF. I like the more verbose CASE.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply