From: | stan <stanb(at)panix(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Probably a newbie question |
Date: | 2019-08-10 18:57:14 |
Message-ID: | 20190810185714.GA23952@panix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
--
"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
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-08-10 19:10:10 | Re: Probably a newbie question |
Previous Message | stan | 2019-08-10 18:53:19 | FW: Undelivered Mail Returned to Sender |