Re: cannot find source of "add missing from" error

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(at)svana(dot)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.