Postgres - using select statement inside CASE WHEN

Question

Inside case when condition I am executing select statement & if returns anything then I need to get it's value but I am getting error

ERROR:  missing FROM-clause entry for table "us"

query is..

SELECT u.user_id,
       CASE
           WHEN
                  (SELECT us.attr_value
                   FROM user_setting us
                   WHERE us.user_id = u.user_id) IS NOT NULL THEN us.attr_value
           ELSE
                  (SELECT gus.attr_value
                   FROM global_user_setting gus
                   WHERE gus.attr_key='key')
       END
FROM user u
WHERE u.user_id IN (1,
                          2,3)

Error comes at IS NOT NULL THEN us.attr_value I understood the issue but couldn't find how to get that value outside select statement?

Stefan Haberl · Accepted Answer · 2024-12-17 12:18:03Z

17

Try:

COALESCE((SELECT us.attr_value
               FROM user_setting us
               WHERE us.user_id = u.user_id),
         (SELECT gs.attr_value
               FROM global_user_setting gs
               WHERE gus.attr_key='key'))

instead. The reason for the problem is, that the binding of the us alias is not visible outside of the sub-select (as it is used in a "scalar" context). The whole subselect is basically a single expression, which will yield a single value.

Another (IMHO better) approach would be to left-join on the enrollment_settings table:

SELECT u.user_id,
       COALESCE(us.attr_value, (SELECT gus.attr_value
                                FROM global_user_setting gs
                                WHERE gus.attr_key='key'))
FROM user u LEFT JOIN user_settings us ON us.user_id = u.user_id
WHERE u.user_id IN (1, 2, 3)

I assume here, that this join would yield at most a single row per row of user.

edited Dec 17, 2024 at 12:18

Stefan Haberl

10.7k8 gold badges79 silver badges91 bronze badges

answered Apr 25, 2014 at 15:16

Dirk

31.1k8 gold badges86 silver badges103 bronze badges

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

Collectives™ on Stack Overflow

Postgres - using select statement inside CASE WHEN

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