| ORA-01427 single row subquery returns more than one row [message #296462] |
Sun, 27 January 2008 12:48  |
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  |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
