Re: FW: Undelivered Mail Returned to Sender - Mailing list pgsql-general

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