CODEWITHSUNDEEP
postgresql
j.man
j.man

Reputation: 11

Error "missing FROM-clause entry for table"

When I use the following code, I get the error "missing FROM-clause entry for table zp". Does anyone know how I can eliminate this error?

SELECT SUM(zp.WP*et.GIS)
FROM (SELECT DISTINCT zp.HHNR,zp.ZIELPNR FROM mobility.zielpersonen zp
FULL OUTER JOIN mobility.etappen et
ON zp.HHNR=et.HHNR AND zp.ZIELPNR=et.ZIELPNR
) as examp

Upvotes: 1

Views: 4636

Answers (2)

PhilS
PhilS

Reputation: 1661

I would propose this as a guess:

SELECT SUM(exam.WP * exam.GIS)
FROM (SELECT DISTINCT   zp.HHNR,
                        zp.ZIELPNR, 
                        zp.WP, 
                        et.GIS
    FROM mobility.zielpersonen zp
    FULL OUTER JOIN mobility.etappen et
    ON zp.HHNR=et.HHNR AND zp.ZIELPNR=et.ZIELPNR
) as examp

This is includes WP and GIS in the DISTINCT query and might or might not be what you want.

However, I think the most natural approach in this situation would be to just group by HHNR and ZIELPNR and calulate the SUM of the other values. This can be done by using this query:

SELECT zp.HHNR, zp.ZIELPNR, SUM(zp.WP*et.GIS) AS CalcResult
FROM mobility.zielpersonen zp
LEFT JOIN mobility.etappen et
ON zp.HHNR=et.HHNR 
AND zp.ZIELPNR=et.ZIELPNR
GROUP BY zp.HHNR, zp.ZIELPNR

I replaced the OUTER JOIN with a LEFT JOIN, as I see no point in calculating/grouping the SUM for NULL values.

If neither of these options is what you want, please clarify the question!

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172538

The zp and et table cannot be referred here:

SELECT SUM(zp.WP*et.GIS)

They can be referred only inside the subquery scope.

Simply try to do this:

SELECT SUM(examp.WP*examp.GIS)
FROM (SELECT DISTINCT zp.HHNR,zp.ZIELPNR FROM mobility.zielpersonen zp
FULL OUTER JOIN mobility.etappen et
ON zp.HHNR=et.HHNR AND zp.ZIELPNR=et.ZIELPNR
) as examp

Upvotes: 0

Related Questions