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_statess, 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,
dealershipsd, 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@eddie gary]$ psql -f goole1.sql
DROP
psql:goole1.sql:45: ERROR: Relation "r" does not exist
[gary@eddie gary]$
I tried using the table name instead of the alias but instead got the error:
[gary@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@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
