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

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.