OraFAQ Forum: SQL & PL/SQL » ORA-01427: single-row subquery returns more than one row

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427: single-row subquery returns more than one row (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01427: single-row subquery returns more than one row [message #634705]

Fri, 13 March 2015 10:34

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

When I try to execute the belwo query i am getting the below error

ORA-01427: single-row subquery returns more than one row

Is any other way to re-write the below query.

SELECT *
  FROM all_source
 WHERE LOWER(text) LIKE
         (SELECT '''' || '%' || LOWER(table_name) || '%' || ''''
            FROM dba_tables
           WHERE owner = 'CRIS_WAREHOUSE_USER')

Report message to a moderator

Re: ORA-01427: single-row subquery returns more than one row [message #634706 is a reply to message #634705]

Fri, 13 March 2015 10:45

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do not use a subquery, join the "tables".

Report message to a moderator

Re: ORA-01427: single-row subquery returns more than one row [message #634709 is a reply to message #634706]

Fri, 13 March 2015 10:55

na.dharma@gmail.com
Messages: 82
Registered: May 2008
Location: bangalore

Member

all_source and all_tables there is no join clause column

Report message to a moderator

Re: ORA-01427: single-row subquery returns more than one row [message #634710 is a reply to message #634709]

Fri, 13 March 2015 10:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, sorry, I wrote too fast what I meant but it would not happen if you told us what you want to do instead we have to reverse engineer it.
So what is your purpose EXACTLY.

The join is not directly on the views columns but on your (LIKE) test.

[Updated on: Fri, 13 March 2015 11:00]

Report message to a moderator

Re: ORA-01427: single-row subquery returns more than one row [message #634711 is a reply to message #634705]

Fri, 13 March 2015 11:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I already answered your question on OTN.

SY.

Report message to a moderator

Previous Topic:	2 count query results manipulation
Next Topic:	Query the newest records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:04:37 CDT 2024