1

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?

  • Does anybody else have never used alias in CASE statement? –  Dec 19 '22 at 18:55
  • Looks very strange what you are doing there. Please provide some sample data, expected result and explain which logic you want to build. This weird query doesn't tell us what you are aiming to do. – Jonas Metzler Dec 19 '22 at 19:04
  • @JonasMetzler Actually I just want to use alias in CASE statement. For simplifying things, you can give an example using any query you want. Thanks in advance. –  Dec 19 '22 at 19:36
  • No, I think you should show your input and the expected outcome and let people here think about the best solution for this specific issue. "I want something because I want it" is no issue which should be solved. – Jonas Metzler Dec 19 '22 at 19:39

1 Answers1

0

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;
Albina
  • 1,901
  • 3
  • 7
  • 19
  • Thanks a lot amigo. What about using t2 ? Should I use the same alias name for that table as t ? –  Dec 19 '22 at 19:55
  • @stack There is no need to use the `t2` alias and you can remove occurrences of it from the query. And please, provide schema and test data. We have no idea about your expected outcome. – Albina Dec 19 '22 at 20:08
  • Wat do you mean with "occurrences of it" ? Could you update your answer by removing occurrences of it ? –  Dec 19 '22 at 20:25
  • Amigo? Any reply pls? –  Dec 19 '22 at 20:30
  • @stack done, check it now. – Albina Dec 19 '22 at 20:46
  • Thanks a lot, but would it be also good practice using table alias even if there is not multiple column with the same name in 2 tables? –  Dec 19 '22 at 20:52
  • I mean is `SELECT SUM(tr.amount) FROM trans tr WHERE u.mail = tr.paid_to` also good? –  Dec 19 '22 at 20:52
  • @stack I think it's redundant in your case. If it's more readable for you, please use aliases. Check these 'debates' about aliases: https://dba.stackexchange.com/questions/5989/is-table-aliasing-a-bad-practice – Albina Dec 19 '22 at 21:20