Skip site navigation (1) Skip section navigation (2)

subselect prob in view

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: subselect prob in view
Date: 2004-06-21 10:10:39
Message-ID: 200406211110.39940.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
Hi folks,

I've got the view:

create view nrequest_details as
  select r.r_id, r_registration, r.r_chassis, r.r_vehicle, 
r.r_fuel,r.r_pack_mats,
         r.r_delivery, r_delivery::date-now()::date as r_remaining, 
         r.r_created, r.r_completed,
         d.d_des, de.de_des,
         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
         t.t_id, t.t_des,
         s.s_id, s.s_des,
         c.c_id, c.c_des
     from requests r, users u, request_types t, 
          request_states s, dealerships d, departments de, customers c
     where r_d_id = d.d_id and
           r_s_id = s.s_id and
           r_c_id = c.c_id and
           r_t_id = t.t_id and
           r_d_id = d.d_id and
           r_de_id = de.de_id and
           r_u_id = u.u_id;

to which I want to add a count (2 eventually), so that it becomes:

create view nrequest_details as
  select r.r_id, r_registration, r.r_chassis, r.r_vehicle, 
r.r_fuel,r.r_pack_mats,
         r.r_delivery, r_delivery::date-now()::date as r_remaining, 
         r.r_created, r.r_completed,
         d.d_des, de.de_des,
         u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target,
         t.t_id, t.t_des,
         s.s_id, s.s_des,
         c.c_id, c.c_des,
         co.count as comments
--       cor.count as comments_unseen
     from requests r, users u, request_types t, 
          request_states s, dealerships d, departments de, customers c
          left outer join (select co_r_id, count(co_r_id) from comments group 
by co_r_id) co on
            co.co_r_id = r.r_id
--        left outer join (select co_r_id, count(co_r_id) from comments where 
cor_viewed is null
--                         group by co_r_id) co on
--          co.co_r_id = r.r_id
     where r_d_id = d.d_id and
           r_s_id = s.s_id and
           r_c_id = c.c_id and
           r_t_id = t.t_id and
           r_d_id = d.d_id and
           r_de_id = de.de_id and
           r_u_id = u.u_id;

but I get the error:

[gary(at)eddie gary]$ psql -f goole1.sql
DROP
psql:goole1.sql:45: ERROR:  Relation "r" does not exist
[gary(at)eddie gary]$

I tried using the table name instead of the alias but instead got the error:

[gary(at)eddie gary]$ psql -f goole1.sql
psql:goole1.sql:1: ERROR:  view "nrequest_details" does not exist
psql:goole1.sql:45: NOTICE:  Adding missing FROM-clause entry for table 
"requests"
psql:goole1.sql:45: ERROR:  JOIN/ON clause refers to "requests", which is not 
part of JOIN
[gary(at)eddie gary]$

which at least makes sense.

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     


Responses

pgsql-sql by date

Next:From: Richard HuxtonDate: 2004-06-21 10:14:59
Subject: Re: Strange behaviour updating primary key column.
Previous:From: V i s h a l Kashyap @ [Sai Hertz And Control Systems]Date: 2004-06-21 09:42:17
Subject: Re: Function Parameters - need help !!!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group