1

I'm trying to build a report of all users and their score for a particular course for a Moodle v2.7 database (which uses PostgreSQL 9.3. I am getting the following error and have looked at questions and other examples that produce the same error, but none of them provide an answer for my specific problem. Not sure what I'm overlooking here.

ERROR: Invalid reference to FROM-clause entry for table "mdl_user"

SELECT 
    mdl_user.lastname as LastName,
    mdl_user.firstname as FirstName,
    Grr.finalgrade as Grade,
    REPLACE(Grr.itemname,'COURSE: ','') as Course,
    TO_CHAR(TO_TIMESTAMP(Grr.timemodified), 'MM/DD/YYYY') as Completed
FROM
    public.mdl_user LEFT JOIN (
        SELECT 
            mdl_grade_grades.finalgrade,
            mdl_grade_items.itemname,
            mdl_grade_grades.userid,
            mdl_grade_grades.timemodified
        FROM
            public.mdl_grade_items LEFT JOIN public.mdl_grade_grades
                ON public.mdl_grade_items.id = public.mdl_grade_grades.itemid
        WHERE
            mdl_grade_grades.userid = mdl_user.id AND
            mdl_grade_items.itemname LIKE 'COURSE: Lock Out Tag Out: ECP'
    ) AS Grr ON Grr.userid = public.mdl_user.id
ORDER BY
    mdl_user.lastname,
    mdl_user.firstname
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Drew Chapin
  • 7,779
  • 5
  • 58
  • 84
  • Do you need to wrap `ID` in quotes? it appears as a KEY word list for postgresql http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html perhaps wrapping it in quotes will eliminate the issue. ***As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away.*** – xQbert Mar 06 '15 at 21:17
  • @xQbert, I don't think it's the problem because other somewhat simpler queries work that use the `ID` field in the same way. But if it is a keyword, then it should be for quoted as you have stated. I will try quoting it and get back to you if it fixes it. – Drew Chapin Mar 06 '15 at 21:20
  • What's the actual data type of `timemodified`? – Erwin Brandstetter Mar 07 '15 at 07:50
  • @xQbert: Quoted identifiers are a common cause for grief, but that doesn't seem to be the case here. – Erwin Brandstetter Mar 07 '15 at 08:22

2 Answers2

4

@avk is right. But there is more. Properly simplified query:

SELECT u.lastname
     , u.firstname
     , g.finalgrade AS grade
     , right(i.itemname, -8) AS course
     , to_char(to_timestamp(g.timemodified), 'MM/DD/YYYY') AS completed
FROM   public.mdl_user         u
LEFT   JOIN
      (public.mdl_grade_items  i
  JOIN public.mdl_grade_grades g ON g.itemid = i.id
                                AND i.itemname = 'COURSE: Lock Out Tag Out: ECP'
      ) ON g.userid = u.id
ORDER  BY 1,2;

  • It's subtly wrong to use LIKE where the left operand is supposed to be a straight match and not a pattern. This can lead to confusing side effects, like involuntarily interpreting special characters or this (example from just today):

  • You had a LEFT JOIN combined with a predicate on the right table, which force-converts it to a plain [INNER] JOIN. I moved the predicate to the join clause to fix that. Related:

  • Replace the subquery completely. You can use parentheses in the FROM clause to achieve what you are after.

  • Unquoted, mixed-case identifiers are pointless.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This is close but does not produce the desired output. For any entry in the `mdl_user` table where there is a match from the `JOIN` enclosed by parenthesis, it lists the entry twice. Once with the course name (`itemname`) and once with a `null` value for the course name. All of the entries from `mdl_user` table should only be listed one time. – Drew Chapin Mar 09 '15 at 13:24
  • @druciferre: That shouldn't be. Are you sure you are using a plain `[INNER] JOIN` between `mdl_grade_items` and `mdl_grade_grades` inside the parentheses, and *not* a `RIGHT JOIN` (which could lead to a result like you describe)? – Erwin Brandstetter Mar 09 '15 at 13:31
  • Nevermind, it seems there was a `deleted` field in the `mdl_user` table and I need to ignore entries that have a value of `1`. This works now. Thanks! – Drew Chapin Mar 09 '15 at 13:53
1

In the subselect Grr, remove the mdl_grade_grades.userid = mdl_user.id.
This is causing the error and it is already joined in the AS Grr ON Grr.user.id=public.mdl_user.id line anyway.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
avk
  • 871
  • 1
  • 9
  • 22