Missing FROM-clause entry for a table

Question

I wrote the following SQL statement to get data from two tables gendata & TrainingMatrix:

SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll, "TrainingName", "Institute")"gendata"."Employee Name","gendata"."Position", "gendata"."Department",  "TrainingMatrix".* 
FROM "TrainingMatrix" JOIN "gendata" ON "TrainingMatrix".payroll = "gendata".payroll 
ORDER  BY payroll, "TrainingName", "Institute" ,"TrainingDate" DESC NULLS LAST) AS foo;

It works fine, but I need to filter the records more by:

WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0 
AND  EXTRACT(YEAR FROM  "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);

So, the orginal SQL statement will be:

SELECT * FROM (SELECT DISTINCT ON ("TrainingMatrix".payroll, "TrainingName", "Institute")"gendata"."Employee Name","gendata"."Position", "gendata"."Department",  "TrainingMatrix".* 
FROM "TrainingMatrix" JOIN "gendata" ON "TrainingMatrix".payroll = "gendata".payroll 
ORDER  BY payroll, "TrainingName", "Institute" ,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingMatrix"."ExpiryDate" - current_date <= 0 
AND  EXTRACT(YEAR FROM  "TrainingMatrix"."ExpiryDate") = EXTRACT(YEAR FROM current_date);

But I got this error:

ERROR: missing FROM-clause entry for table "TrainingMatrix" LINE 3: ...te" ,"TrainingDate" DESC NULLS LAST) AS foo WHERE "TrainingM...

I am using PostgreSQL. Any advise guys?

Answer 1

100% what @a_horse already said. Plus a couple more things:

• Format your query so it's easy to read and understand for humans before you try to debug. Even more so, before you post in a public forum.

• Use table aliases, especially with your unfortunate CaMeL-case names to make it easier to read.

• Provide your table definitions or at least table-qualify column names in your query, so we have a chance to parse it. Your immediate problem is already fixed in the query below. You would also replace ?. accordingly:

  • t .. alias for "TrainingMatrix"
  • g .. alias for gendata

SELECT *
FROM  (
    SELECT DISTINCT ON (t.payroll, ?."TrainingName", ?."Institute")
           g."Employee Name", g."Position", g."Department",  t.* 
    FROM   "TrainingMatrix" t
    JOIN   gendata          g  ON g.payroll = t.payroll 
    ORDER  BY t.payroll, ?."TrainingName", ?."Institute"
         , ?."TrainingDate" DESC NULLS LAST
    ) AS foo
WHERE  foo."ExpiryDate" - current_date <= 0 
AND    EXTRACT(YEAR FROM  foo."ExpiryDate") = EXTRACT(YEAR FROM current_date);

But there's more.

• Like @a_horse wrote, it's a bad idea to use illegal identifiers that have to be double-quoted all the time. But an identifier with enclosed space character is even worse: "Employee Name". That's one step away from home-made SQL-injection.

• The way your additional filters are phrased is bad for performance.

  WHERE  "ExpiryDate" - current_date <= 0 
  

  Is not sargable and therefore can't use a plain index. Leaving that aside, it is also more expensive than it needs to be. Use instead:

  WHERE "ExpiryDate" >= current_date
  

  Similar for your 2nd expression, which should be rewritten to:

  WHERE  "ExpiryDate" >= date_trunc('year', current_date)
  AND    "ExpiryDate"  < date_trunc('year', current_date) + interval '1 year'
  

  Combining both, we can strip a redundant expression:

  WHERE  "ExpiryDate" >= current_date
  AND    "ExpiryDate"  < date_trunc('year', current_date) + interval '1 year'
  

• Your question is ambiguous. Do you want to apply the additional filter before DISTINCT or after? Different result.
  Assuming before DISTINCT, you don't need a subquery - which removes the cause for your immediate problem: No different alias for the subquery.

All together:

SELECT DISTINCT ON (t.payroll, "TrainingName", "Institute") 
       g."Employee Name", g."Position", g."Department", t.* 
FROM   "TrainingMatrix" t
JOIN   gendata          g USING (payroll)
WHERE  t."ExpiryDate" >= current_date
AND    t."ExpiryDate" <  date_trunc('year', current_date) + interval '1 year'
ORDER  BY t.payroll, "TrainingName", "Institute", "TrainingDate" DESC NULLS LAST

Answer 2

As you have wrapped your actual query into a derived table (the select .. from (...) as foo) your "table" isn't called TrainingMatrix any longer. You need to reference it using the alias you use for the derived table:

select *
from (
  ... you original query ..
) as foo
where foo."ExpiryDate" - current_date <= 0
and   extract(year from foo."ExpiryDate") = extract(year from current_date)

---

Btw: I would recommend you stop using quoted identifiers "ExpiryDate" using case-sensitive names usually gives you more trouble than it's worth.