From: | Julien Rouhaud <rjuju123(at)gmail(dot)com> |
---|---|
To: | Eagna <eagna(at)protonmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Problem with LATERAL |
Date: | 2022-10-13 07:30:11 |
Message-ID: | Y0e+g9CaCSic5YHD@jrouhaud |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
On Thu, Oct 13, 2022 at 07:05:48AM +0000, Eagna wrote:
>
> relatively simple one would have thought! I tried to convert this into a Postgres query as follows:
>
> SELECT o.order_id,
> o.total_price - COALESCE(sub.paid, 0)
> FROM _order o
> LEFT JOIN LATERAL (
> SELECT SUM(p.amount) AS paid
> FROM payment p
> WHERE p.order_id = o.order_id
> ) AS sub
> WHERE o.total_price > ISNULL(sub.paid, 0); -- << line 10 - Error occurs!
>
> but I receive the error:
>
> ERROR: syntax error at or near "WHERE"
> LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);
There error here is because a JOIN clause requires a join condition. Adding an
"ON true" is probably what you want. You would also need to change isnull()
with coalesce().
The final query should be:
SELECT o.order_id,
o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS sub ON true
WHERE o.total_price > coalesce(sub.paid, 0);
From | Date | Subject | |
---|---|---|---|
Next Message | Eagna | 2022-10-13 08:04:03 | Re: Problem with LATERAL |
Previous Message | Eagna | 2022-10-13 07:05:48 | Problem with LATERAL |