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?

Fritzfritze

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.

Beera

Answer 2

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);

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;

Barkeeper

Answer 3

I created person table, then inserted 2 rows into it as shown below:

CREATE TABLE person (
  id INTEGER,
  name VARCHAR(20)
);

INSERT INTO person (id, name) 
VALUES (1, 'John'), (2, 'David');

Then, trying to get person table with SELECT statement, omitting FROM clause got the same error as shown below:

postgres=# SELECT person.id, person.name;
ERROR:  missing FROM-clause entry for table "person"
LINE 1: SELECT person.id, person.name;

But, I could get person table with SELECT statement and FROM clause as shown below:

postgres=# SELECT person.id, person.name FROM person;
 id | name
----+-------
  1 | John
  2 | David
(2 rows)

Rosado