Is there a way to do this without CASE expression?-CodePudding

I would like to avoid so often using CASE expressoin, especially for not so complicated logic like here. Is there a better way?

CASE
WHEN lower(player_status::text) in ( 'active'::text,'live'::text)
THEN true
ELSE false
END

Can I get the same output without CASE?

Thanks!

CodePudding user response：

Actually you don't need the CASE expression.
You can directly select the boolean expression:

SELECT COALESCE(lower(player_status::text), '') IN ('active'::text, 'live'::text) 
FROM ...

The function COALESCE() is used to prevent the expression to return NULL in case player_status is NULL.

If player_status is not nullable it can be omitted:

SELECT lower(player_status::text) IN ('active'::text, 'live'::text) 
FROM ...

CodePudding user response：

So I gather the end result you are looking for is a list of usernames and user statuses, but for users having the status "active" or "live" you wish to display the boolean true instead of the value actually stored in the database, right? The accepted answer works, but personally I find it a little easier to read using select constants:

SELECT name, true as player_status
FROM players.player_data
WHERE player_status IN ('active', 'live')

This will present only active/live players and their status will be displayed as true. If you need the query to return all players, regardless of status, while still making the text substitution for active players, you can simply union together the above query with its inverse, though I'm not sure this is any less complex or readable than the other two proposed solutions in this thread:

SELECT name, true as player_status
FROM players.player_data
WHERE player_status IN ('active', 'live')

UNION ALL

SELECT name,false
FROM players.player_data
WHERE (player_status is null) 
   OR (player_status NOT IN ('active', 'live'))

That all said, personally, I'd probably make this substitution at the UI layer, not in the database query, which would make the whole matter moot, though that's more a matter of personal preference than.