Re: 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