Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01427 single row subquery returns more than one row
ORA-01427 single row subquery returns more than one row [message #296462] Sun, 27 January 2008 12:48 Go to next message
hillaryruth
Messages: 1
Registered: January 2008
Location: Maine
Junior Member
When I run this query in Access against Oracle DB I get the error shown above. Would someone show me how to fix this? Thanks!

SELECT a.case_id
INTO A
FROM NOLDBA_RPT_CASE_CHG_IND a
WHERE ((a.aft_case_status IN ('O','I')
AND a.sys_curr_date = (SELECT MAX(z.sys_curr_date)
FROM NOLDBA_RPT_CASE_CHG_IND z
WHERE z.case_id = a.case_id
AND z.sys_curr_date <= #01/18/2008#)
AND a.time_code = (SELECT MAX(y.time_code)
FROM NOLDBA_RPT_CASE_CHG_IND y
WHERE y.case_id = a.case_id
AND y.sys_curr_date = a.sys_curr_date)
AND (a.sys_userid_fld2 = (SELECT MAX(x.sys_userid_fld2)
FROM NOLDBA_RPT_CASE_CHG_IND x
WHERE x.case_id = a.case_id
AND x.sys_curr_date = a.sys_curr_date)
OR a.sys_userid_fld2 = (SELECT v.sys_userid_fld2
FROM NOLDBA_RPT_CASE_CHG_IND v
WHERE v.case_id = a.case_id
AND v.sys_curr_date = a.sys_curr_date
AND v.time_code = a.time_code)))
OR (a.aft_case_status = 'C'
AND a.sys_curr_date BETWEEN #01/19/2007# AND #01/18/2008#
AND a.bef_case_status IN ('O','I')))
Re: ORA-01427 single row subquery returns more than one row [message #296466 is a reply to message #296462] Sun, 27 January 2008 15:03 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This code is unreadable.

I wanted to apply the [code] tags to preserve formatting, but guess what? Your query is really written that way!

Please, read the OraFAQ Forum Guide, especially its "How to format your post" section. Then someone might take a look at it.

Here's a free advice, which requires no effort at all:
Oracle
ORA-01427 single-row subquery returns more than one row

Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

In other words: make sure your query returns exactly one record as a result. You might need to add the additional condition into the WHERE clause; or, if you're lucky, a DISTINCT keyword might help.
Re: ORA-01427 single row subquery returns more than one row [message #296487 is a reply to message #296462] Sun, 27 January 2008 23:25 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ie, solution depends on how the data is sitting in the table and nature of the business rule .

Thumbs Up
Rajuvan.

[Updated on: Sun, 27 January 2008 23:25]

Report message to a moderator

Previous Topic: Find the nth record from a table
Next Topic: Chptr 9 more PL/SQL tools the oracle PL/SQL 101 book
Goto Forum:
  


Current Time: Fri Dec 09 21:12:42 CST 2016

Total time taken to generate the page: 0.08562 seconds