Обсуждение: Probably a newbie question

Поиск
Список
Период
Сортировка

Probably a newbie question

От
stan
Дата:
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

Re: Probably a newbie question

От
Ron
Дата:
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.

Re: Probably a newbie question

От
Rob Sargent
Дата:


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.


Re: Probably a newbie question

От
Melvin Davidson
Дата:
>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!

Re: Probably a newbie question

От
"David G. Johnston"
Дата:
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. 

Re: Probably a newbie question

От
stan
Дата:
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