CODEWITHSUNDEEP
sqlpostgresqlms-accessview
willemorley
willemorley

Reputation: 57

PostgreSQL view won't work - column doesnt exist

Hi I am trying to migrate an access database into postgresql and everything was going well until i tried this view. I am wanting it to create a new column called 'CalculatedHours'. And as Im new to postgresql I am slightly confused. Heres the code that I keep putting into pgAdmin and getting the error...

SELECT "SessionsWithEnrolmentAndGroups"."SessionID", 
        "Assignments"."Staff", 
        "SessionsWithEnrolmentAndGroups"."groups", 
        "SessionsWithEnrolmentAndGroups"."SessionQty",
        "SessionsWithEnrolmentAndGroups"."Hours",
        "SessionsWithEnrolmentAndGroups"."Weeks",
        "Assignments"."Percentage",
        "Assignments"."AdditionalHours", 
        Round((coalesce(("groups"),1)*("SessionQty")*("Hours")*("Weeks")
                   *("Percentage"))) AS CalculatedHours, 
        (CalculatedHours)+coalesce(("AdditionalHours"),0) AS "TotalHours"
FROM "SessionsWithEnrolmentAndGroups" 
INNER JOIN "Assignments" 
ON "SessionsWithEnrolmentAndGroups"."SessionID" = "Assignments"."SessionID";

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You cannot access column aliases in the same select where they are defined. I would suggest a subquery:

SELECT t.*,
       (CalculatedHours)+coalesce(("AdditionalHours"), 0) AS "TotalHours"
FROM (SELECT eag."SessionID", a, eag."groups", eag."SessionQty",
             eag."Hours", eag."Weeks", a."Percentage", a."AdditionalHours", 
             Round((coalesce(("groups"),1)*("SessionQty")*("Hours")*("Weeks")*("Percentage"))) AS CalculatedHours
      FROM "SessionsWithEnrolmentAndGroups" eag INNER JOIN 
           "Assignments" a
           ON eag."SessionID" = a."SessionID"
     ) t;

Your queries would also be much more readable using table aliases and getting rid of the escape characters (double quotes) unless they are really, really needed.

Upvotes: 2

Related Questions