ansaurus

Question

Answer 1

+4 A:

Try this:

select x.ColA,
       X.ColB / denominator as colB,
       X.ColC / denominator as colC,
       X.ColD / denominator as colD,
       X.ColE / denominator as colE
from (
     select x.ColA, X.ColB, X.ColC, X.ColD, X.ColE,
            case when x.optA = 'AB' or x.optA = 'FG' or x.optA = 'LM' or x.optA = 'QR' then 100 else 900 End as denominator
     From TableA
     )

In standard SQL, you are restricted to one condition per column.

Frank 2010-07-30 19:34:15

Actually my calculation " X.ColB / denominator as X.colB" is more complicated, with a nested case, which also has a subtraction in one of the cases, than what I have shown above.

Needs Help 2010-07-30 19:44:00

@Needs Can you show your actual query then so we don't waste time on answers irrelevant to your needs?

Martin Smith 2010-07-30 19:56:26

@Needs Help: This approach can be adapted, as Martin's answer below shows.

Frank 2010-07-30 20:46:16

Answer 2

A:

You say in a comment that your actual needs are different from that presented in your question.

One other approach which might help readability/maintainability is to push the complicated condition statement into a CTE then you can check a simpler condition in the main query rather than repeating the complex one.

WITH cte AS
(
select case when x.optA = 'AB' or x.optA = 'FG' or x.optA = 'LM' or x.optA = 'QR' then 1 else 0 end as cond,
ColB,
ColC,
ColD,
ColE
FROM x
)

select x.ColA as ColA ,
case when cond=1 then  X.ColB / 100 else  X.ColB / 900 As ColB
case when cond=1 then  X.ColC / 100 else  X.ColC / 900 As ColC
case when cond=1 then  X.ColD / 100 else  X.ColD / 900 As ColD
case when cond=1 then  X.ColE / 100 else  X.ColE / 900 As ColE
From cte

Martin Smith 2010-07-30 20:03:43

I like this approach better.

Needs Help 2010-07-30 20:18:50

ansaurus

tags:

views:

answers:

sql case / if statement

related questions