FW: Undelivered Mail Returned to Sender

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

FW: Undelivered Mail Returned to Sender

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


Reply | Threaded
Open this post in threaded view
|

Re: FW: Undelivered Mail Returned to Sender

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

Re: FW: Undelivered Mail Returned to Sender

rob stone-2
In reply to this post by stan-9
Hello,

On Sat, 2019-08-10 at 14:53 -0400, stan 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 -----
>


You are selecting from a table named bom_item, but further down you
have

WHERE bom_item is NOT NULL

Shouldn't that be WHERE bom_item.some_column_name IS NOT NULL?

Cheers,
Rob




Reply | Threaded
Open this post in threaded view
|

Re: FW: Undelivered Mail Returned to Sender

David Rowley-3
In reply to this post by stan-9
On Sun, 11 Aug 2019 at 06:53, stan <[hidden email]> 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 ,

> 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?

Looks to me like your WHERE clause is wrong in the subquery. "WHERE
bom_item.vendor_key ="  surely that should be just "WHERE vendor_key =
" (assuming that's the primary key column of the vendor table).

Also, you've mentioned you've only a single record in the
mfg_vendor_relationship, so the error can't be due to multiple records
matching in the mfg_vendor_relationship table. However, given the
unique constraint on that table includes 3 columns and you're just
filtering on 2 of them, then it would only take some rows in there
with the same mfg_key and project_key values but a different
vendor_key to get the same error from that part of the query.  If that
shouldn't happen, then perhaps your UNIQUE constraint should not
contain the vendor_key column.  You'd have to explain what you need in
more detail for someone to be able to help you fix that.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services