CODEWITHSUNDEEP
mysqlsqlexcel
Nicolai Iversen
Nicolai Iversen

Reputation: 41

How to apply multiple case conditions on a single parameter in SQL query

I have the following (shortened) code:

WHERE
analysis.debtor.gender=CASE
WHEN ?='Men' THEN 'MALE'
ELSE 'FEMALE'
END

This works but I would like to have multiple cases for the same parameter, ?, ie. 

WHEN ?='Men' THEN 'MALE', WHEN ?='Female' THEN Female ELSE

it should include both men and woman. How can I implement this in my code?

Upvotes: 0

Views: 77

Answers (4)

MtwStark
MtwStark

Reputation: 4058

you was almost right with your sintax, you can write it also this way

WHERE analysis.debtor.gender = 
    case 
        when ?='Men' then 'MALE' 
        when ?='Female' then 'FEMALE' 
        when ?='Cross' then 'XGENDER' 
        else 'UNKNOWN' 
    end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you have only exact equality conditions, then you can do:

WHERE analysis.debtor.gender = (CASE ? WHEN 'Men' THEN 'MALE' WHEN 'Women' THEN 'FEMALE' END)

I'm not a big fan of putting CASE in the WHERE clause. It can be hard to maintain. An alternative is to use a params subquery:

FROM (SELECT ? as val) params CROSS JOIN
     . . .
WHERE (analysis.debtor.gender = 'MALE' and val = 'Men') OR
      (analysis.debtor.gender = 'FEMALE' and val = 'Women')

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5155


You can have a case statement as

CASE COLUMN_NAME
WHEN 'Men' THEN 'MALE'
WHEN 'Female' THEN 'FEMALE'
ELSE 'MEN AND WOMEN'
END

If you provide your complete query, will be useful to help you

Upvotes: 0

Boris Schegolev
Boris Schegolev

Reputation: 3701

Use an OR to add another condition:

WHERE
    analysis.debtor.gender=CASE WHEN ?='Men' THEN 'MALE' ELSE 'FEMALE' END OR
    (? NOT IN ('Men', 'Female'))

Upvotes: 1

Related Questions