Hello,
I have a slightly involved view that draws from other views.
Upon insertion into the database (creation) it complains of
ERROR: missing FROM-clause entry in subquery for table "v_basic_person"
and I can't for the life of it find why (I know I can work
around this with a postgresql.conf option).
The view that's erroring out and the view that's being
complained about is found in the attachments. The whole lot
of other tables and functions is found here:
http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/
(the function _() is found in gmI18N.sql BTW)
Can anyone help me pinpoint which relation is missing in
what FROM clause ?
(This project is a medical practice management system.)
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
create view clin.v_hx_family as
-- those not linked to another patient as relative
select
vpi.pk_patient as pk_patient,
vpi.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.modified_when as modified_when,
chxf.modified_by as modified_by,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
null::integer as pk_narrative_condition,
null::integer as pk_relative_identity,
hxfi.name_relative as name_relative,
hxfi.dob_relative as dob_relative,
hxfi.condition as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
clin.v_pat_items vpi,
clin.clin_hx_family chxf,
clin.hx_family_item hxfi,
v_basic_person vbp
where
vpi.pk_item = chxf.pk_item
and
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition is null
and
hxfi.fk_relative is null
UNION
-- those linked to another patient as relative
select
vpi.pk_patient as pk_patient,
vpi.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.modified_when as modified_when,
chxf.modified_by as modified_by,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
null::integer as pk_narrative_condition,
hxfi.fk_relative as pk_relative_identity,
vbp.firstnames || ' ' || vbp.lastnames as name_relative,
vbp.dob as dob_relative,
hxfi.condition as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
clin.v_pat_items vpi,
clin.clin_hx_family chxf,
clin.hx_family_item hxfi,
v_basic_person vbp
where
vpi.pk_item = chxf.pk_item
and
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition is null
and
hxfi.fk_relative = v_basic_person.pk_identity
UNION
-- those linked to a condition of another patient being a relative
select
vpn.pk_patient as pk_patient,
vpn.pk_health_issue as pk_health_issue,
chxf.clin_when as clin_when,
chxf.modified_when as modified_when,
chxf.modified_by as modified_by,
chxf.fk_encounter as pk_encounter,
chxf.fk_episode as pk_episode,
chxf.narrative as relationship,
chxf.soap_cat as soap_cat,
chxf.pk as pk_clin_hx_family,
chxf.fk_hx_family_item as pk_hx_family_item,
hxfi.fk_narrative_condition as pk_narrative_condition,
vpn.pk_patient as pk_relative_identity,
vbp.firstnames || ' ' || vbp.lastnames as name_relative,
vbp.dob as dob_relative,
vpn.narrative as condition,
hxfi.age_noted as age_noted,
hxfi.age_of_death as age_of_death,
hxfi.is_cause_of_death as is_cause_of_death
from
clin.clin_hx_family chxf,
clin.hx_family_item hxfi,
v_basic_person vbp,
clin.v_pat_narrative vpn
where
hxfi.pk = chxf.fk_hx_family_item
and
hxfi.fk_narrative_condition = vpn.pk_narrative
and
hxfi.fk_relative is null
and
vbp.pk_identity = vpn.pk_patient
;create view v_basic_person as
select
i.pk as pk_identity,
n.id as n_id,
i.title as title,
n.firstnames as firstnames,
n.lastnames as lastnames,
i.dob as dob,
i.cob as cob,
i.gender as gender,
_(i.gender) as l10n_gender,
i.karyotype as karyotype,
i.pupic as pupic,
case when i.fk_marital_status is null
then 'unknown'
else (select ms.name from marital_status ms, identity i1 where
ms.pk=i.fk_marital_status and i1.pk=i.pk)
end as marital_status,
case when i.fk_marital_status is null
then _('unknown')
else (select _(ms1.name) from marital_status ms1, identity i1
where ms1.pk=i.fk_marital_status and i1.pk=i.pk)
end as l10n_marital_status,
i.fk_marital_status as pk_marital_status,
n.preferred as preferred,
i.xmin as xmin_identity
from
identity i,
names n
where
i.deceased is NULL and
n.active = true and
n.id_identity = i.pk
;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
