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);
