How do you write a case expression that has 2 conditions ?

What I'm trying to say in the code below is when the color is red and the quality is bad then price. And when color is red but quality is Good then price *2.

There's only 2 options for quality i.e Good and Bad

Code Example:

Case when color = 'red' and quality = 'Bad' then price
     else price * 2
end as RED

CodePudding user response：

Here is the basic structure of a CASE:

case
    when A then X
    when B then Y
    else Z
end

You can have as many "when/then" lines as you want (or your dbms supports). You can put any boolean expression in place of A or B. You can put any expression in place of X, Y, and Z, including another CASE.

So you could simply list out all your combinations like this:

case
    when color = 'red' and quality = 'Good' then price*2
    when color = 'red' and quality = 'Bad' then price
    when color = 'blue' and quality = 'Good' then price*3
    when color = 'blue' and quality = 'Bad' then price/2
    else null
end as price

Or you can nest them like this:

case
    when color = 'red' then
        case
            when quality = 'Good' then price*2
            else price
        end
    when color = 'blue' then
        case
            when quality = 'Good' then price*3
            else price/2
        end
    else 
        null
end as price

CodePudding user response：

Considering your example:

select case color 
                when 'red' 
                     then (case quality when 'Bad' then price when 'Good' then price*2 end) 
                when 'white' 
                     then (case quality when 'Bad' then price 10 when 'Good' then (price 10)*2 end)
    --              [... other conditions ...]
                else 0 
            end as Price

look at the syntax the case ... end can be used in differen ways.

case field when value1 then result1 when value2 then result2 ... else result0 end

or

case when field=value1 then result1 when field=value2 then result2 ... else result0 end

each results (result1, result2, result0) may itself be a case ... end statement.