postgres WITH query claims no FROM-clause

Question

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

The alias `cte` is clearly *not* in the FROM clause... – Sep 13 '13 at 15:58 — , Sep 13 '13 at 15:58

score 1 · Accepted Answer · answered Sep 13 '13 at 15:53

1

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

answered Sep 13 '13 at 15:53

bma

9,424
2
33
22

Thanks. I was under the incorrect assumption that using the WITH like that actually put the cte into the query. Didn't realize I needed to explicitly join it, though of course that makes sense once I see it. – Gargoyle Sep 13 '13 at 16:05
You don't need to JOIN to the CTE necessarily, you can also reference it in the WHERE clause (similar to what you did), but via subqueries. Eg. `WHERE o.cident = (select cident from cte limit 1)`. – bma Sep 13 '13 at 16:07
This answer is incomplete and could be improved by adding explanation to how the code addresses the issue. Further, the code example could be reduced to the portion which is different than op's original code sample. – Madbreaks Jan 12 '21 at 23:38

postgres WITH query claims no FROM-clause

1 Answers1