On Sun, 11 Aug 2019 at 06:53, stan <stanb@panix.com> wrote:
>
> I apologize for asking, what I suspect will turn out to be a newbie
> question, but I have managed to get myself quite confused on this.
>
> I am defining a view as follows
>
>
> CREATE OR REPLACE view purchase_view as
> select
> project.proj_no ,
> qty ,
> mfg_part.mfg_part_no ,
> mfg.name as m_name ,
> mfg_part.descrip as description ,
> (
> SELECT
> name
> FROM
> vendor
> WHERE
> bom_item.vendor_key =
> (
> SELECT
> vendor_key
> FROM
> mfg_vendor_relationship
> WHERE
> bom_item.mfg_key = mfg_key
> AND
> prefered = TRUE
> AND
> bom_item.project_key = project_key
>
> )
> )
> as v_name ,
> ERROR: more than one row returned by a subquery used as an expression
>
> Can someone please enlighten me as to the error of my ways?
Looks to me like your WHERE clause is wrong in the subquery. "WHERE
bom_item.vendor_key =" surely that should be just "WHERE vendor_key =
" (assuming that's the primary key column of the vendor table).
Also, you've mentioned you've only a single record in the
mfg_vendor_relationship, so the error can't be due to multiple records
matching in the mfg_vendor_relationship table. However, given the
unique constraint on that table includes 3 columns and you're just
filtering on 2 of them, then it would only take some rows in there
with the same mfg_key and project_key values but a different
vendor_key to get the same error from that part of the query. If that
shouldn't happen, then perhaps your UNIQUE constraint should not
contain the vendor_key column. You'd have to explain what you need in
more detail for someone to be able to help you fix that.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services