Nested case conditionals in PostgreSQL, syntax error?

Question

I use PostgreSQL and I would like to combine these two case conditions, but when I uncomment the code, I get a syntax error. This is not a difficult instruction. I want to divide or multiply the obtained number depending on the condition. How can I enter it so that the code is compiled?

SELECT
SUM(

CASE
    WHEN transactions.recipient_account_bill_id = recipientBill.id AND recipientBill.user_id = 2
    THEN 1
    ELSE -1
END * transactions.amount_money

/* CASE
    WHEN senderBillCurrency.id = recipientBillCurrency.id
    THEN NULL
    ELSE
        CASE
            WHEN recipientBillCurrency.base = true
            THEN /
            ELSE *
        END senderBillCurrency.current_exchange_rate
END */

) as TOTAL

FROM transactions

LEFT JOIN bills AS senderBill ON senderBill.id = transactions.sender_account_bill_id
LEFT JOIN bills AS recipientBill ON recipientBill.id = transactions.recipient_account_bill_id
LEFT JOIN currency as senderBillCurrency ON senderBillCurrency.id = senderBill.currency_id
LEFT JOIN currency as recipientBillCurrency ON recipientBillCurrency.id = recipientBill.currency_id

WHERE 2 IN (senderBill.id, recipientBill.id)

Lukas Eder · Accepted Answer · 2020-04-13 08:37:53Z

3

You cannot create dynamic SQL expressions like that. A CASE expression cannot return an operator as if SQL was some sort of macro language. You can only return an expression.

You already used the following approach using 1 and -1 with your multiplication. Why not also use it with N and 1/N:

<some expression> * CASE WHEN condition THEN 1 / N ELSE N END

Or in your case:

<some expression> * CASE
    WHEN senderBillCurrency.id = recipientBillCurrency.id THEN 1
    WHEN recipientBillCurrency.base THEN 1 / senderBillCurrency.current_exchange_rate
    ELSE senderBillCurrency.current_exchange_rate
END

Notice, you can put several WHEN clauses in a CASE expression. No need to nest them

edited Apr 13, 2020 at 8:37

answered Apr 13, 2020 at 8:34

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

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

Collectives™ on Stack Overflow

Nested case conditionals in PostgreSQL, syntax error?

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