Thread: cannot find source of "add missing from" error
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
Attachment
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.
Attachment
On Wed, Dec 07, 2005 at 01:50:37PM +0100, Martijn van Oosterhout wrote: > > from > > v_basic_person vbp <---------- > > where > > hxfi.fk_relative = v_basic_person.pk_identity <----------- Duh. It got to be something simple which I just couldn't see. Thanks for pointing that out. > 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. Fixed, too. How wonderful a resource this list is. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346