How to assign select into variable and use it in case statement

Question

I have query in postgres:

SELECT CASE WHEN (Select money from Bank)  is not null THEN (Select money from Bank) ELSE -1 end;

Is there any possibilities not repeat this subquery in 'THEN' ? I would like to do something like:

SELECT CASE WHEN ((Select money from Bank) = MONEY)  is not null THEN MONEY ELSE -1 end;

I need to do this in one Select statement.

Gordon Linoff · Accepted Answer · 2020-10-09 12:32:46Z

2

One method is to use coalesce():

SELECT COALESCE( (Select money from Bank), -1)

The subquery might still be run twice (due to the semantics of COALESCE()) -- I'm not sure how Postgres optimizes this.

You can fix this by moving the logic to the from clause:

select coalesce(b.money, -1)
from (Select money from Bank) b

Note: This will not return any rows if Bank is empty. My guess, though, is that you have simplified the query and there are more conditions. If bank really has at most one row, then you can just use:

select coalesce(max(b.money), -1)
from bank b

If this is really a more complicated query, you might need a lateral join:

select . . ., coalesce(b.money, -1)
from . . . left join lateral
     (Select money from Bank) b;

edited Oct 9, 2020 at 12:32

answered Oct 9, 2020 at 11:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

404 Over a year ago

First example and second aren't equivalent if the subquery returns no results; first version will return 1 row with -1 and second will return 0 rows.

Collectives™ on Stack Overflow

How to assign select into variable and use it in case statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related