Home » SQL & PL/SQL » SQL & PL/SQL » ora 4127 single subquery returns more than one row" (Oracle Sql)
ora 4127 single subquery returns more than one row" [message #419652] Mon, 24 August 2009 16:02 Go to next message
sunibom
Messages: 2
Registered: August 2009
Junior Member
I am updating a Master view which has many tables and joins,but my need of hour is I need to update that view by adding additional columns in it
It went well untill i got gatecrashed with a problem.

Here is what i am looking for...

I have 3 tables

below is how i am joining them......
select
( SELECT ET3301.VALUE_1
FROM PROJECTS P_EXTPROJ3301
Left Outer Join PROJECT_EXT_ATTR_MULTI PEAM_EXTPROJ3301 ON
P_EXTPROJ3301.PROJECT_ID = PEAM_EXTPROJ3301.PROJECT_ID
Left Outer Join ENCODED_LIST_ITEMS ELI3301 ON
PEAM_EXTPROJ3301.ENCODED_LIST_ITEM_ID = ELI3301.ENCODED_LIST_ITEM_ID
Left Outer Join ENCODED_TITLES ET3301 ON
ELI3301.Encoded_Title_ID = ET3301.Encoded_Title_ID
WHERE (P_EXTPROJ3301.PROJECT_ID = P.Project_ID)
) "Master PI#"

FROM PROJECTS P
WHERE
P.Project_Type_ID IN (501, 601, 101)
When i run this query i am getting error "ora 4127 single subquery returns more than one row"

I did some digg in ti data where i found that there are multiple value,s or PI#'s for a single project id so how can i get multiple rows in a single row with comma as a seperator.

appreciate your help.
Re: ora 4127 single subquery returns more than one row" [message #419655 is a reply to message #419652] Mon, 24 August 2009 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
SELECT (SELECT et3301.value_1 
        FROM   projects p_extproj3301 
               LEFT OUTER JOIN project_ext_attr_multi peam_extproj3301 
                 ON p_extproj3301.project_id = peam_extproj3301.project_id 
               LEFT OUTER JOIN encoded_list_items eli3301 
                 ON peam_extproj3301.encoded_list_item_id = eli3301.encoded_list_item_id 
               LEFT OUTER JOIN encoded_titles et3301 
                 ON eli3301.encoded_title_id = et3301.encoded_title_id 
        WHERE  (p_extproj3301.project_id = p.project_id)) "Master PI#" 
FROM   projects p 
WHERE  p.project_type_id IN (501,601,101)


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

> how can i get multiple rows in a single row with comma as a seperator
WRONG! SQL can not handle your flight of fancy.
Re: ora 4127 single subquery returns more than one row" [message #419665 is a reply to message #419652] Mon, 24 August 2009 23:20 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so how can i get multiple rows in a single row with comma as a seperator.

Search for "wm_concat", "stragg" and so on "pivot" answer.

Regards
Michel
Previous Topic: select from sql
Next Topic: With RELY DISABLE option not able use the FK constraints
Goto Forum:
  


Current Time: Tue Dec 06 08:39:23 CST 2016

Total time taken to generate the page: 0.21948 seconds