CASE Statement Opinion

18 Aug 2005 5:59 PM

WebBuilder451

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

18 Aug 2005 6:08 PM

Perayu

Or, 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

Show quote

"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

18 Aug 2005 6:09 PM

JT

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.

Show quote

"WebBuilder451" <WebBuilder***@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

18 Aug 2005 6:14 PM

--CELKO--

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.

18 Aug 2005 6:50 PM

WebBuilder451

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

Show quote

"--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.
>
>