conditional row naming in PostgreSQL

Question

How can i conditionally name a row in psql . If the h_te.to_pay_to_customer > h_te.pay_from_customer then i want to name the row to 'to_give_to_customer' if h_te.pay_from_customer > h_te.to_pay_to_customer then i want to name the row as 'to_receive_from_customer' else the name of the row should be balanced.

Here is what I tried:

SELECT u_c.name,
       sum(CASE
         WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
          (h_te.to_pay_to_customer - h_te.pay_from_customer)
         WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
         (h_te.pay_from_customer - h_te.to_pay_to_customer)
         ELSE 0
       end)  case  when h_te.to_pay_to_customer > h_te.pay_from_customer then as   to_give_to_customer WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN as  to_receive_from_customer else balanced,
       u_c.owner_id
FROM   home_transactionentries h_te
       INNER JOIN home_transaction h_t
               ON h_te.transaction_id = h_t.id
       INNER JOIN users_customer u_c
               ON u_c.id = h_t.customer_id
WHERE  u_c.owner_id = 1
GROUP  BY u_c.name,
          u_c.owner_id;

but it is giving me a syntax error

ERROR:  syntax error at or near "case"
LINE 8:        end)  case  when h_te.to_pay_to_customer > h_te.pay_f...

Note:

h_te is home_transactionentries table

h_t is home_transaction table

u_c is user_customer table

Siddhi · Accepted Answer · 2021-09-01 05:01:35Z

You cannot have an output column NAME conditional. The way you are handling the case statement will not work. You can have a separate column to conditionally display notes instead. Please check for_conditional_column_name below to show remarks/note.

SELECT 
    u_c.name,
    sum(
        CASE
            WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN
                (h_te.to_pay_to_customer - h_te.pay_from_customer)
            WHEN h_te.pay_from_customer > h_te.to_pay_to_customer THEN
                (h_te.pay_from_customer - h_te.to_pay_to_customer)
        ELSE 
            0
        END) AS pay,  
        CASE 
            WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN 'to_give_to_customer' 
            WHEN h_te.pay_from_customer  > h_te.to_pay_to_customer THEN 'to_receive_from_customer' 
        ELSE 
            'balanced'
        END AS for_conditional_column_name,
        u_c.owner_id
FROM
    home_transactionentries h_te
INNER JOIN 
    home_transaction h_t
ON 
    h_te.transaction_id = h_t.id
INNER JOIN 
    users_customer u_c
ON 
    u_c.id = h_t.customer_id
WHERE  
    u_c.owner_id = 1
GROUP BY 
    u_c.name,
    u_c.owner_id, 
        CASE 
            WHEN h_te.to_pay_to_customer > h_te.pay_from_customer THEN 'to_give_to_customer' 
            WHEN h_te.pay_from_customer  > h_te.to_pay_to_customer THEN 'to_receive_from_customer' 
        ELSE 
            'balanced'
        END

Collectives™ on Stack Overflow

conditional row naming in PostgreSQL

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