Thread: FW: Undelivered Mail Returned to Sender

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 ,
    /*
    vendor.name as v_name ,
    */
    cost_per_unit ,
    costing_unit.unit,
    need_date ,
    order_date ,
    recieved_date ,
    po_no ,
    po_line_item 
from 
    bom_item
right join project on 
    project.project_key = bom_item.project_key
inner join mfg_part on 
    mfg_part.mfg_part_key = bom_item.mfg_part_key
inner join vendor on 
    vendor.vendor_key = bom_item.vendor_key
inner join costing_unit on 
    costing_unit.costing_unit_key = bom_item.costing_unit_key
inner join mfg on 
    mfg.mfg_key = bom_item.mfg_key 
WHERE bom_item is NOT NULL  
ORDER BY 
    project.proj_no ,
    mfg_part
    ;

Most of the tables are pretty much simple key -> value relationships for
normalization. I can add the create statements to this thread if it adds
clarity.

The exception is:



CREATE TABLE mfg_vendor_relationship (
    mfg_vendor_relationship_key_serial         integer DEFAULT nextval('mfg_vendor_relationship_key_serial')
    PRIMARY KEY ,
    mfg_key       integer NOT NULL,
    vendor_key    integer NOT NULL,
    project_key   integer NOT NULL,
    prefered      boolean NOT NULL ,
    modtime           timestamptz DEFAULT current_timestamp ,
    FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
    FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
    FOREIGN KEY (project_key) references project(project_key) ,
    CONSTRAINT mfg_vendor_constraint 
                UNIQUE (
            mfg_key , 
            vendor_key , 
            project_key
        )
);


I am down to having a single row in the mfg_vendor_relationship as follows:

 mfg_vendor_relationship_key_serial | mfg_key | vendor_key | project_key |
 prefered |            modtime            
 ------------------------------------+---------+------------+-------------+----------+-------------------------------
                 164 |       1 |          1 |           2 |
                 t        | 2019-08-10 14:21:04.896619-04

But trying to do a select * from this view returns:

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?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin


----- End forwarded message -----

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

Hello,

On Sat, 2019-08-10 at 14:53 -0400, stan 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 ,
>     /*
>     vendor.name as v_name ,
>     */
>     cost_per_unit ,
>     costing_unit.unit,
>     need_date ,
>     order_date ,
>     recieved_date ,
>     po_no ,
>     po_line_item 
> from 
>     bom_item
> right join project on 
>     project.project_key = bom_item.project_key
> inner join mfg_part on 
>     mfg_part.mfg_part_key = bom_item.mfg_part_key
> inner join vendor on 
>     vendor.vendor_key = bom_item.vendor_key
> inner join costing_unit on 
>     costing_unit.costing_unit_key = bom_item.costing_unit_key
> inner join mfg on 
>     mfg.mfg_key = bom_item.mfg_key 
> WHERE bom_item is NOT NULL  
> ORDER BY 
>     project.proj_no ,
>     mfg_part
>     ;
> 
> Most of the tables are pretty much simple key -> value relationships
> for
> normalization. I can add the create statements to this thread if it
> adds
> clarity.
> 
> The exception is:
> 
> 
> 
> CREATE TABLE mfg_vendor_relationship (
>     mfg_vendor_relationship_key_serial         integer DEFAULT
> nextval('mfg_vendor_relationship_key_serial')
>     PRIMARY KEY ,
>     mfg_key       integer NOT NULL,
>     vendor_key    integer NOT NULL,
>     project_key   integer NOT NULL,
>     prefered      boolean NOT NULL ,
>     modtime           timestamptz DEFAULT current_timestamp ,
>     FOREIGN KEY (mfg_key) references mfg(mfg_key) ,
>     FOREIGN KEY (vendor_key) references vendor(vendor_key) ,
>     FOREIGN KEY (project_key) references project(project_key) ,
>     CONSTRAINT mfg_vendor_constraint 
>                 UNIQUE (
>             mfg_key , 
>             vendor_key , 
>             project_key
>         )
> );
> 
> 
> I am down to having a single row in the mfg_vendor_relationship as
> follows:
> 
>  mfg_vendor_relationship_key_serial | mfg_key | vendor_key |
> project_key |
>  prefered |            modtime            
>  ------------------------------------+---------+------------+------
> -------+----------+-------------------------------
>                  164 |       1 |          1
> |           2 |
>                  t        | 2019-08-10 14:21:04.896619-
> 04
> 
> But trying to do a select * from this view returns:
> 
> 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?
> 
> 
> -- 
> "They that would give up essential liberty for temporary safety
> deserve
> neither liberty nor safety."
>                         -- Benjamin Franklin
> 
> 
> ----- End forwarded message -----
> 


You are selecting from a table named bom_item, but further down you
have

WHERE bom_item is NOT NULL

Shouldn't that be WHERE bom_item.some_column_name IS NOT NULL?

Cheers,
Rob

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