Good Morning all,
I am having a problem understanding a simple LATERAL join - I'm working on grasping them.
All tables and data are at the bottom of this question and on the fiddles, SQL Server (working) and Postgres (not
working).
SQL Server fiddle - https://dbfiddle.uk/hjBBd87B
Postgres fiddle - https://dbfiddle.uk/PihnqTwG
I have the following - create table scripts and sample data are at end of this question and on the fiddles.
The query in question which works on SQL Server is the following:
SELECT o.order_id,
o.total_price - COALESCE(p.paid, 0) AS remaining
FROM _order o
CROSS APPLY (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS p
WHERE o.total_price > ISNULL(p.paid, 0);
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);
I would be grateful if I could get a working query and also for an explanation as to what it is I'm doing incorrectly?
The payment table has no primary key. Any input on the suitability of creating a surrogate one would also be
appreciated.
Please let me know if there's any important information missing.
Rgs and TIA,
E.
CREATE TABLE _order
(
order_id INT NOT NULL PRIMARY KEY,
total_price INT NOT NULL
);
INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000), (4, 4000);
CREATE TABLE payment
(
order_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id)
);
CREATE INDEX pt_order_id_ix ON payment (order_id); -- normal indexing of foreign key field
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4