On Wed, Dec 07, 2005 at 10:10:40AM +0100, Karsten Hilbert wrote: > 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).
<snip> > 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 <----------- > You can't alias a table and then use the original table name because that refers to a different table. That's why this error message exists. You should use vbp.pk_identity. Note, one of the legs of your union has v_basic_person in the FROM but doesn't use it. The effect is probably hidden due to your use of UNION rather than UNION ALL. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
pgp2ItGaoaKbE.pgp
Description: PGP signature
