Re: Problem with LATERAL

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