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.

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.

You might get away with adding
group by vendor_key
if it turns out you’re simply getting many copies of vendor key from that inner select.
Run it alone to see.

ERROR: more than one row returned by a subquery used as an expression

Without knowledge as to the contents of your data, the best I can suggest is
to use *SELECT DISTINCT* in your subqueries.

On Sat, Aug 10, 2019 at 3:42 PM Rob Sargent <robjsargent@gmail.com> wrote:

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.

You might get away with adding
group by vendor_key
if it turns out you’re simply getting many copies of vendor key from that
inner select.
Run it alone to see.

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

On Saturday, August 10, 2019, stan <stanb@panix.com> wrote:

Sorry, I got the list address wrong the first time, and when I corected it,
I forget to fix the subject line.

This subject line isn’t materially better...subjects should reflect the
technical content of the message, not its sender.

David J.

On Sat, Aug 10, 2019 at 02:57:14PM -0400, stan wrote:

Sorry, I got the list address wrong the first time, and when I corrected 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
;

Thanks to the kind, bright people on this list, I have solved my problem.
The basic issue was that my from clause was on the wrong table.

Thanks to everyone who spent their time helping me out on this!
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin