MySql CASE expression with two input columns

Question

I'm looking to use a case expression in mysql with two columns as input. This is how I would like to format the code. All code snippets use Set @a = 0; Set @b = 0; at the start, although those values can be modified. My own use case has several values each could be (more than 2 each).

SELECT 
    CASE (@a , @b)
        WHEN (0 , 0) THEN 'Both zero'
        WHEN (1 , 0) THEN 'a is 1'
        WHEN (0 , 1) THEN 'b is 1'
        WHEN (1 , 1) THEN 'both 1'
        ELSE NULL
    END;

When using the case statement this way, MySql throws Error Code: 1241. Operand should contain 1 column(s). For reference, both of the following work, which makes me curious why the above doesn't.

SELECT 
    IF((@a , @b) = (0 , 0),
        'Both zero',
        IF((@a , @b) = (1 , 0),
            'a is 1',
            IF((@a , @b) = (0 , 1),
                'b is 1',
                IF((@a , @b) = (1 , 1), 'both 1', NULL))));

and

SELECT 
    CASE
        WHEN (@a , @b) = (0 , 0) THEN 'Both zero'
        WHEN (@a , @b) = (1 , 0) THEN 'a is 1'
        WHEN (@a , @b) = (0 , 1) THEN 'b is 1'
        WHEN (@a , @b) = (1 , 1) THEN 'both 1'
        ELSE NULL
    END;

So, the question: Can I use a CASE expression like the first code snippet when doing a multi-column comparison? If so, please provide an example. If not, why not?

The 3rd method is completely viable for my own use case, but if @a and @b were select statements, it wouldn't be. The ideal would be a solution that:

Only runs inputs once, if they were select statements.
Can be easily expanded upon for any number of inputs.
Is agnostic to the input types (IE (@a,@b) = (0,'!@#$:abc') is a valid case to check).

Not possible. You cannot have "lists of values" in a case statement. case takes one SINGLE value, and that's it. — Marc B
– Marc B, Commented Sep 8, 2016 at 16:59

Barmar · Accepted Answer · 2016-09-08 17:10:26Z

0

You can't use the short version of CASE with multiple values. You need to use the long version that tests both conditions with AND

CASE 
    WHEN @a = 0 AND @b = 0 THEN 'Both zero'
    WHEN @a = 1 AND @b = 0 THEN 'a is 1'
    WHEN @a = 0 AND @b = 1 THEN 'b is 1'
    WHEN @a = 1 AND @b = 1 THEN 'Both 1'
    ELSE NULL
END

Another option is to concatenate them.

CASE CONCAT(@a, @b)
    WHEN '00' THEN 'Both zero'
    WHEN '10' THEN 'a is 1'
    WHEN '01' THEN 'b is 1'
    WHWN '11' THEN 'Both 1'
    ELSE NULL
END

answered Sep 8, 2016 at 17:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql CASE expression with two input columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related