>ERROR: more than one row returned by a subquery used as an _expression_
Well, we really do not have any insight as to the contents of your data, but
have you thought about using SELECT DISTINCT in your subquerys?
On Sat, Aug 10, 2019 at 2:53 PM stan <stanb@xxxxxxxxx> 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 -----
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
