Search Postgresql Archives

Re: FW: Undelivered Mail Returned to Sender

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, 11 Aug 2019 at 06:53, stan <stanb@xxxxxxxxx> 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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux