CASE Statement Opinion

I have the following case statement:
CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
WHEN 0 THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
It works ok, but i'd like t oknow if there is a better way to do this. It
feels messy, but that doesn't mean there's a better way.
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesOr, you could write:
CASE when i.idxhstRSXOPos = i2.idxhstRSXOPos THEN 'md2'
ELSE
CASE i.idxhstRSXOLastAct
WHEN 1 then 'mdG'
WHEN 2 then 'mdR'
WHEN 3 then 'mdG'
WHEN 4 then 'mdR'
END
END as rsXOxoflg
Perayu
"WebBuilder451" wrote:

> I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||When populating a calculated column, typically for reporting purposes, I've
written embedded case expressions more twisted than that. An expression like
that probably won't be indexed by the query optimizer, but so long as you
are not placing it in the where or group by clause, I expect it would only
be a marginal performance hit. One other option would be to return
idxhstRSXOPos, i2.idxhstRSXOPos, and idxhstRSXOLastAct in the query result
and let client application do the calculation.
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:245254F2-39A7-4DB9-AF86-4B86D8869685@.microsoft.com...
>I have the following case statement:
> CASE (i.idxhstRSXOPos - i2.idxhstRSXOPos)
> WHEN 0 THEN 'md2'
> ELSE
> CASE i.idxhstRSXOLastAct
> WHEN 1 then 'mdG'
> WHEN 2 then 'mdR'
> WHEN 3 then 'mdG'
> WHEN 4 then 'mdR'
> END
> END as rsXOxoflg
> It works ok, but i'd like t oknow if there is a better way to do this. It
> feels messy, but that doesn't mean there's a better way.
> thanks
> kes
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||It looks good to me. Nesting CASE expressions is legal as long as the
data types are correct. If you want to flatten it out, you can:
CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
END AS rsXOxoflg
CASE expressions evaluate the WHEN clauses in order. This will do the
same thing as yours.|||this is what i was looking for.
Not to say the other answers were not good.
Thank You everyone
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"--CELKO--" wrote:

> It looks good to me. Nesting CASE expressions is legal as long as the
> data types are correct. If you want to flatten it out, you can:
> CASE WHEN (i.idxhstRSXOPos - i2.idxhstRSXOPos) = 0 THEN 'md2'
> WHEN i.idxhstRSXOLastAct IN (1, 3) THEN 'mdG'
> WHEN i.idxhstRSXOLastAct IN (2, 4) THEN 'mdR'
> END AS rsXOxoflg
> CASE expressions evaluate the WHEN clauses in order. This will do the
> same thing as yours.
>