Re: Probably a newbie question

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.