CODEWITHSUNDEEP
mysqlsql
Madhu Sareen
Madhu Sareen

Reputation: 549

Using SQL's CASE for a large number of conditions?

What I want to do is this:

case_1  case_2  case_3  Final
0           0    0      0
0           0    1      3
0           1    0      2
1           0    0      1
1           1    0      2
1           0    1      3
0           1    1      3
1           1    1      3

That means when case_1 is 0, case_2 is 0 and case_3 is 0, the final col has value 0. Similarly, when case_1 is 1, case_2 is 1 and case_3 is 1, the final cols will be 3. And so forth.

And what I ended up typing in SQL which is awkward:

Select *,
case when case_1>0 and case_2>0 and case_3>0 then 3 else 0,
case when case_1>0 and case_2>0 and case_3=0 then 2 else 0,  
case when case_1>0 and case_2=0 and case_3=0 then 1 else 0,
....
....
....
from mytable;

Now this is seriously bad, I know that. Can there be better way of such coding?

Upvotes: 0

Views: 51

Answers (3)

O. Jones
O. Jones

Reputation: 108686

For what it's worth, electrical engineering knows this problem as "generating a Boolean expression from a truth table."

I'm going a different direction from the other answerers.

Create yourself a tiny lookup table with eight rows and four columns, like this

SELECT * FROM final

| case_1 | case_2 | case_3 | Final |
|--------|--------|--------|-------|
|      0 |      0 |      0 |     0 |
|      0 |      0 |      1 |     3 |
|      0 |      1 |      0 |     2 |
|      1 |      0 |      0 |     1 |
|      1 |      1 |      0 |     2 |
|      1 |      0 |      1 |     3 |
|      0 |      1 |      1 |     3 |
|      1 |      1 |      1 |     3 |

Then join to it to your main data table to do your lookup of the final value, like this (http://sqlfiddle.com/#!9/4de009/1/0).

  SELECT a.Name, b.Final
    FROM test a
    JOIN final b ON a.case_1 = b.case_1 
                AND a.case_2 = b.case_2
                AND a.case_3 = b.case_3

Performance? Not a problem on an eight-row lookup table. SQL is made for this.

Flexibility? If your rules for computing Final change all you have to do is update the table. You don't have to do the Boolean expression simplification again.

Complexity? Well, yes, it's more complex than a nested bunch of CASE or IF statements. But it's easier to read.

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30819

From the example, it looks like the priority is case 3 -> case 2 -> case 1. In which case, you can do something like this:

SELECT *,
CASE WHEN case_3 > 0 THEN 3
     WHEN case_2 > 0 THEN 2
     WHEN case_1 > 0 THEN 1
ELSE 0 END AS `Final`
FROM table;

Upvotes: 2

Serg
Serg

Reputation: 22811

Looks like you want the rightmost position of nonzero column, if any

 select *,
    case when case_3>0 then 3 else 
      case when case_2>0 then 2 else
       case when case_1>0 then 1 else 0 end
      end
    end final
 from tbl

Upvotes: 1

Related Questions