Re: Probably a newbie question - Mailing list pgsql-general

On 8/10/19 1:57 PM, stan wrote:
> Sorry, I got the list address wrong the first time, and when I corected it,
> I forget to fix the subject line.
>
> 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?
>
>

I'd look here:

    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
                
        )


-- 
Angular momentum makes the world go 'round.