PostgreSQL : Cannot use column alias in CASE statement-CodePudding

I cannot use column alias in CASE clause in the following example:

SELECT 
  ((SELECT SUM(t2.amount) FROM trans t2 WHERE u.mail = t2.paid_to)-
  (SELECT SUM(t2.amount) FROM trans t2 WHERE u.mail = t2.paid_by)) AS "balance",
  (CASE WHEN balance < u.credit_limit THEN 'YES' ELSE 'NO' END) AS "result"
  
FROM user u 
LEFT JOIN trans t ON u.mail = t.paid_to OR u.mail = t.paid_by

How can I solve this problem?

Another question, should I use a different alias for inner selects (t2) or can I also use the same alias (t) as the outer select for trans table?

CodePudding user response：

It is problematic to use the alias "balance" inside of a CASE statement because you try to use alias as a field within the same query and Postgresql restricts it.

You can try to write a subquery. As DB schema wasn't provided, I couldn't test this query:

WITH t AS (
 SELECT 
   ((SELECT SUM(amount) FROM trans WHERE u.mail = paid_to)-
   (SELECT SUM(amount) FROM trans WHERE u.mail = paid_by)) AS balance,
   u.credit_limit 
 FROM user u 
 LEFT JOIN trans t ON u.mail = t.paid_to OR u.mail = t.paid_by
)
SELECT t.balance, 
       CASE WHEN t.balance < t.credit_limit THEN 'YES' ELSE 'NO' END AS result
FROM t;