On Sun, 11 Aug 2019 at 06:53, 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 , > 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
