Probably a newbie question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Probably a newbie question

stan-9
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


Reply | Threaded
Open this post in threaded view
|

Re: Probably a newbie question

Ron-2
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.


lup
Reply | Threaded
Open this post in threaded view
|

Re: Probably a newbie question

lup


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.


Reply | Threaded
Open this post in threaded view
|

Re: Probably a newbie question

Melvin Davidson-5
>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 <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|

Re: Probably a newbie question

David G Johnston
In reply to this post by stan-9
On Saturday, August 10, 2019, stan <[hidden email]> 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. 
Reply | Threaded
Open this post in threaded view
|

Re: Probably a newbie question

stan-9
In reply to this post by stan-9
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