Equivalent in MS Sql

Hum Dum

What is equivalent of MS Access IFF in MS Sql for this expression

IIf([ID_SECTION]=1,1,IIf([ID_SECTION]=2,2,IIf([ID_SECTION]=3,[ID_Tariff_Gender],0)))

Will it be like

CASE WHEN [ID_SECTION]=1 THEN 1
ELSE
CASE WHEN [ID_SECTION]=2 then 2
ELSE
CASE WHEN [ID_SECTION] = 3 then [ID_Tariff_Gender] ELSE 0 END
END
END

Is it correct ? Any comments !!

Geoff Williams

If you want to use a SQL CASE statement, then try the following:

CASE [ID_SECTION]
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN [ID_Tarriff_Gender]
ELSE 0
END

Lost User

Hum Dum wrote:

Is it correct ?

Yes, it works.

Hum Dum wrote:

Any comments !!

In this case, CASE is an overkill and an IF statement works perfectly well.

SilimSayo

I think it isn't overkill you're working on a select statement. [ID_SECTION] is probably a column name ---hint that a select statement is being used.

jschell

Shameel wrote:

In this case, CASE is an overkill and an IF statement works perfectly well.

As a general statement I disgree with that. The IF is an acceptable alternative if CASE didn't exist but CASE exists specifically to deal with situations exactly like that. Given your statement what situation would be correct for CASE then?

Lost User

If you see the example, each nested CASE statement has only one WHEN clause, and this is where I recommended an IF clause which works exactly the same way and is more readable. There are, of course, situations where only CASE can be used and an IF cannot be used, like for example, in a SELECT statement.

SilimSayo

The question refers to a column [ID_SECTION] which implies a select/insert/update/delete statement. [ID_SECTION] couldn't possibly be a variablle since it is not prefixed by '@'.

jschell

Shameel wrote:

If you see the example, each nested CASE statement..,

Your statement wasn't clear but is still incorrect. For the example given it is misusing CASE. The correct solution is to use it correctly.

SilimSayo

jschell wrote:

The correct solution is to use it correctly.

Most of our imports come from abroad! :laugh:

Lost User

SilimSayo wrote:

Most of our imports come from abroad!

Imports become necessary when locals lack quality and become unaffordable! ;-)