case alternative to if in sql

Question

Post reply

case alternative to if in sql

nabajyoti.b

SSCrazy

Points: 2177
More actions
March 2, 2009 at 8:48 am

#200458

how do i convert
IIf([x]="YEN","JPY",IIf([x]="EURO","EUR",[x])) AS InvCurr
to SQL?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442467

March 2, 2009 at 8:49 am

#951905

Have you looked up CASE in BOL (Books Online)?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Hmm, I think it's this:

CASE x

When 'YEN' Then 'JPY'

When 'EURO' Then 'EUR'

End AS InvCurr

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

RBarryYoung (3/2/2009)
Hmm, I think it's this:

Needs the else block as well

CASE x

When 'YEN' Then 'JPY'

When 'EURO' Then 'EUR'

ELSE x

End AS InvCurr

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

RBarryYoung (3/2/2009)
Hmm, I think it's this:
CASE x
When 'YEN' Then 'JPY'
When 'EURO' Then 'EUR'
End AS InvCurr

At least you gave him a correct answer.

In this thread the first person to give an actual answer didn't give an accurate answer.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Gail: Oops, I missed the last "x"! ...

Lynn: Thanks, but as Gail pointed out, I was not entirely correct.

Edit: Said "was", should have said "was not". slight difference... :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

RBarryYoung (3/2/2009)
Gail: Oops, I missed the last "x"! ...
Lynn: Thanks, but as Gail pointed out, I was not entirely correct.
Edit: Said "was", should have said "was not". slight difference... :w00t:

There is, of course, a "depends" here. You gave the OP EXACTLY what he asked for in his post. The ELSE is not required, but it is highly recommended in case you have values that don't match any of your WHEN clauses.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Lynn Pettis (3/2/2009)
You gave the OP EXACTLY what he asked for in his post.

Nope. Barry's solution equates to IIf([x]="YEN","JPY",IIf([x]="EURO","EUR",NULL)) , ie

If X = 'Yen' then 'JPY' ELSE If X = 'Euro' then 'Eur' Else Null,

however the OP's IIF statement was

If X = 'Yen' then 'JPY' ELSE If X = 'Euro' then 'Eur' Else X

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

GilaMonster (3/2/2009)
Lynn Pettis (3/2/2009)
You gave the OP EXACTLY what he asked for in his post.
Nope. Barry's solution equates to IIf([x]="YEN","JPY",IIf([x]="EURO","EUR",NULL)) , ie
If X = 'Yen' then 'JPY' ELSE If X = 'Euro' then 'Eur' Else Null,
however the OP's IIF statement was
If X = 'Yen' then 'JPY' ELSE If X = 'Euro' then 'Eur' Else X

Sorry, I missed the [x] at the end. Must be more exhausted than I thought after this weekend on the pitch.

Well, Barry's answer was still better than that provided in the OP's other post.