Search Postgresql Archives

Re: FW: Undelivered Mail Returned to Sender

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux