CODEWITHSUNDEEP
postgresqlwith-statement
Gargoyle
Gargoyle

Reputation: 10375

postgres WITH query claims no FROM-clause

I've written a simple query that uses a WITH clause, but I'm getting this error:

Error : ERROR: missing FROM-clause entry for table "cte"

Here's the query, where I'm clearly putting a FROM clause. I know this must be simple but I'm just not seeing what I've done wrong. Thanks.

WITH cte AS (
    SELECT cident, "month"
    FROM orders_extended io
    WHERE io.ident = 1    -- 1 will be replaced with a function parameter
)
SELECT *
FROM orders_extended o
WHERE o.cident = cte.cident AND o."month" = cte."month"
ORDER BY o."month" DESC, o.cname

Upvotes: 0

Views: 1605

Answers (1)

bma
bma

Reputation: 9796

The message didn't lie.

WITH cte AS (
    SELECT cident, "month"
    FROM orders_extended io
    WHERE io.ident = 1    -- 1 will be replaced with a function parameter
)
SELECT o.*
FROM orders_extended o
INNER JOIN cte ON (o.cident = cte.cident and o."month" = cte."month")
ORDER BY o."month" DESC, o.cname

Upvotes: 1

Related Questions