CODEWITHSUNDEEP
mysqlsql
bjk116
bjk116

Reputation: 529

Possible to select multiple columns from a single CASE statement?

I have a three boolean columns in a table that determine what values should be fed to a program. I need to feed up to 3 label and values to my program.

As of now I have a 8 cases for each label and each value (since 3 boolean flags means 2^3=8 combinations). Is there a way to make it more succinct?

For example, for my first label, I have 

    case
    WHEN travelExpenseEnabled=0 and otherExpenseEnabled=0 and materialExpenseEnabled = 0 THEN
    ''
    WHEN travelExpenseEnabled=1 and otherExpenseEnabled=0 and materialExpenseEnabled = 0 THEN
    'Estimated Travel Expense'
    WHEN travelExpenseEnabled=1 and otherExpenseEnabled=1 and materialExpenseEnabled = 0 THEN
    'Estimated Travel Expense'
    WHEN travelExpenseEnabled=1 and otherExpenseEnabled=0 and materialExpenseEnabled = 1 THEN
    'Estimated Material'
    WHEN travelExpenseEnabled=1 and otherExpenseEnabled=1 and materialExpenseEnabled = 1 THEN
    'Estimated Material'
    WHEN travelExpenseEnabled=0 and otherExpenseEnabled=1 and materialExpenseEnabled = 0 THEN
    CONCAT('Estimated ', listquotes.otherLabel)
    WHEN travelExpenseEnabled=0 and otherExpenseEnabled=1 and materialExpenseEnabled = 1 THEN
    'Estimated Material'
    WHEN travelExpenseEnabled=0 and otherExpenseEnabled=0 and materialExpenseEnabled = 1 THEN
    'Estimated Material'
    END as optLabel1,

And similar for the first value. This method also means I need to keep my own hierarchy of material > travel > other consistent for each other part of my query, so this will quickly become a headache if I am asked to change order. Is it instead possible to do something like the following

    case
    WHEN travelExpenseEnabled=0 and otherExpenseEnabled=0 and materialExpenseEnabled = 0 THEN
    '' as optLabel1,
            '' as optLabel2,
            '' as optLabel3,
            0.00 as estCost1,
            0.00 as estCost2,
            0.00 as estCost3,
    WHEN travelExpenseEnabled=1 and otherExpenseEnabled=0 and materialExpenseEnabled = 0 THEN
'Estimated Travel' as optLabel1,
'' as optLabel2,
'' as optLabel3,
travelExpense as estCost1,
0.00 as estCost2,
0.00 as estCost3

etc for each case. This way I can set each of the 3 values I need to in just 8 cases, instead of needing to do 8 cases per value. Is this possible? I am using MySQL 5.6

Upvotes: 0

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I would suggests a reference table or derived table:

select t.*,
       ref.optlabel1
from t left join
     (select 0 as travelExpenseEnabled, 0 as otherExpenseEnabled, 0 as materialExpenseEnabled, '' as optlabel1 union all
      select 1, 0, 0, 'Estimated Travel Expense' union all
      ...
     ) ref
     on ref.travelExpenseEnabled = t.travelExpenseEnabled and
        ref.otherExpenseEnabled = t.otherExpenseEnabled and
        ref.materialExpenseEnabled = t.materialExpenseEnabled

You can include all the labels you want in the subquery.

Upvotes: 1

Stephen Montgomery
Stephen Montgomery

Reputation: 11

If you are pulling this from the dB at run time on a UI (I'm assuming) you may wish to flatten your data in advance.

I'd take your code, put that inside a generic select-view, have the app do SELECT {...columnList...} from VIEWNAME where KEY = variable etc

You'll still need to retain & maintain your code base, but it will be centralized in one place.

Upvotes: 1

Related Questions