There’re two error patterns related to ORA-01427 described in this post:
- SELECT with Equal Operator
- Job Failed by Trigger.
I will talk about them respectively in the following sections. In which, the first error pattern is very common and easy to solve. But the second one is not so obvious, you need more patience to solve it.
A. ORA-01427 in SELECT with Equal Operator
If SQL engine expects your subquery to return a single row, it may throw ORA-01427 when the subquery returns more than one row unexpectedly. For example:
SQL> select * from employees where department_id = (select department_id from departments where location_id = 1700);
select * from employees where department_id = (select department_id from departments where location_id = 1700)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.
Solutions
It’s just like that we wanted strictly only one item, but eventually it returned more than we expected.
Equal Sign Wants Only One. But Get More Eventually!
1. Using IN Operator
Then what operator is used to prevent ORA-01427 in SELECT statement? In practice, SELECT should use IN operator instead of = (equal operator) in order to accommodate more than one row returned by the subquery.
SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);
A similar exception that relates to returned number mismatch is ORA-00913: too many values. But they have different error patterns.
More comparison conditions like ANY, SOME or ALL should also be helpful to accept more than one row in your statements so as to avoid ORA-01427 in subquery statement.
2. Using = (Equal) Operator
If you really want to use an equal operator to confine the scope of returned rows as one, you have to limit the number of rows returned of queries to only one row. That’s how we workaround it.
SQL> select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700) where rownum = 1);
An order by clause is more appropriate in the above subquery so as to be close to your expectation.
select * from employees where department_id = (select * from (select department_id from departments where location_id = 1700 order by manager_id) where rownum = 1);
3. Do Not Use DISTINCT with Equal Operator
Adding one distinct keyword before column list cannot prevent ORA-01427, because the number of rows returned by the subquery is still unpredictable.
SQL> select * from employees where department_id = (select distinct department_id from departments where location_id = 1700);
select * from employees where department_id = (select distinct department_id from departments where location_id = 1700)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
No surprises, we saw ORA-01427 in SELECT statement once again.
B. ORA-01427 in Job Failed by Trigger
ORA-12012 and ORA-01427
Same error ORA-01427 could accompany ORA-12012 in the alert log when one scheduled job failed to complete successfully.
ORA-12012: error on auto execute of job 10
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 21
Trigger Caused ORA-01427
According to this error pattern in the above, we saw ORA-01427 in a failed job with ORA-12012. We’d better check some triggers, especially logon and logoff ones to see if there’s any chances to block the job process accidentally. Noticeably, which is Job No. 10 in this case.
You should check the content of the job in the first place to see if there’re any chances to throw ORA-01427. Perhaps you should disable each trigger at a time in order to isolate and identify the cause. Or just turn them off temporarily.
Further reading: How to Kill Session in Logon Trigger
C. MySQL Subquery Returns More Than 1 Row
The same error pattern occurs in MySQL, incorrect number of rows from subquery will result ERROR 1241, let’s see its content:
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = «Subquery returns more than 1 row»
This error is caused by the subquery that must return at most one row but returns multiple rows eventually.
mysql> select * from items where id = (select item_id from orders);
If the subquery returns just one row, the above query will work without errors. If it returns more than one row, we will see error 1242.
The solution is the same as we mentioned in the above sections. Beside IN operator, we can also use ANY, one of comparison conditions to fix the query, for example:
mysql> select * from items where id = any (select item_id from orders);
This is how comparison conditions work for MySQL.
The error ORA-01427 is raised, when a sub-query returns more than one row to an Equality or in-Equality operator.
The reason being there are restrictions on the legal comparison operators.The single row subquery operator are
Symbol | Meaning |
= | equal |
> | greater than |
>= | greater than or equal |
< | less than |
<= | less than or equal |
<> | not equal |
!= | not equal |
So whenever you are using these single row subquery operator, if you return more than one row,it will start throwing the error
How to resolve the error ORA-01427
(1) either we change the query to use Multiple -row Subquery Operators
Multiple-row Sub-query Operators | |
Symbol | Meaning |
IN | equal to any member in a list |
NOT IN | not equal to any member in a list |
ANY | returns rows that match any value on a list |
ALL | returns rows that match all the values in a list |
(2) We look at the query and find out what is happening, why it is returning more rows than expected. Like the way we resolved this error for Concurrent Manager issue. It is generally advisable use the sub-query based on primary key in order to get consistent result,
Lets us take a look at few example
Few Examples
(1) While starting the concurrent Manager in the EBS environment, we were getting below error
Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row ORA-06512: at "APPS.FND_CP_FNDSM", line 29 ORA-06512: at line 1.
This error happens when we have more than two FNDSM of FNDIM defined for the node.So we selected the node name of all the concurrent Manager in the system
Solution
We can resolve the solution manually through below update
SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;
CONCURRENT_QUEUE_NAME | NODE_NAME |
FNDSCH | SUNTEST |
PODAMGR | SUNTEST |
FTE_TXN_MANAGER | SUNTEST |
FNDSM_20151_SUNTEST | SUNTEST |
FNDICM | SUNTEST |
FNDTMTST | SUNTEST |
STANDARD | SUNTEST |
FNDIM_SUNTEST | SUNTEST |
FNDIM_20153_SUNTEST | SUNTEST |
WFMLRSVC | SUNTEST |
XDP_Q_FE_READY_SVC | SUNTEST |
WMSTAMGR | SUNTEST |
INVMGR | SUNTEST |
AMSDMIN | SUNTEST |
FNDSM_SUNTEST | SUNTEST |
We can see there are two FNDSM and FNDIM for the node SUNTEST.
FNDSM_20151_SUNTEST SUNTEST
FNDIM_SUNTEST SUNTEST
FNDIM_20153_SUNTEST SUNTEST
FNDSM_SUNTEST SUNTEST
Lets do the update now
SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME='FNDSM_20151_SUNTEST';
1 row updated.
SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME='FNDIM_20153_SUNTEST';
1 row updated.
SQL> commit;
Commit complete.
SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;
CONCURRENT_QUEUE_NAME | NODE_NAME |
FNDSCH | SUNTEST |
PODAMGR | SUNTEST |
FTE_TXN_MANAGER | SUNTEST |
FNDSM_20151_SUNTEST | |
FNDICM | SUNTEST |
FNDTMTST | SUNTEST |
STANDARD | SUNTEST |
FNDIM_SUNTEST | SUNTEST |
FNDIM_20153_SUNTEST | |
WFMLRSVC | SUNTEST |
XDP_Q_FE_READY_SVC | SUNTEST |
WMSTAMGR | SUNTEST |
INVMGR | SUNTEST |
AMSDMIN | SUNTEST |
FNDSM_SUNTEST | SUNTEST |
Or we can run conc clean and run autoconfig again to resolve the issue
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT;
(2) Another example would be
SELECT * FROM employers WHERE dept_id = (SELECT dept_id FROM dept);
This will fail as we are selecting all dept_id in subquery while expecting one. This will succeed if dept is having one rows only. To resolve we need to add some where clause in subquery so that it returns one value
SELECT * FROM employers WHERE dept_id = (SELECT dept_id FROM dept where location='NEWYORK');
or
We can put in operator in place of equality operator
SELECT * FROM employers WHERE dept_id in (SELECT dept_id FROM dept);
I hope you this content on ORA-01427: single-row subquery returns more than one row. Please do provide feedback
Also Reads
ORA-00900 : Invalid Sql statement : check out this post for the various solutions for ORA-00900 invalid sql statement.Reasons for the error.How to debug and resolve it quickly,
ORA-03113 : Check out method to resolve ORA-03113: end-of-file on communication channel. This error could happen in database startup, running program
ora-29283: invalid file operation : check out this post on how to resolve ORA-29283 : invalid file operation
ORA-29285: file write error : ORA-29285: file write error is the common error while doing file handling operation.Check out this post on various reason and solution on how to solve it
ORA-00054 : ORA-00054: resource busy and acquire with NOWAIT specified with DDL statement,select for update,forms nowait locks,DDL_LOCK_TIMEOUT
Troubleshooting
Problem
Upgrading Maximo 6.2.8 to 7.1.1.10, upgrade fails with this error:
update reportlookup set appname= (select report.appname from report where report.reportnum=reportlookup.reportnum)
;
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
Cause
Upgrade callout UpgradeReportLookupTable assumes that each instance of Report column ReportNum is unique and/or has one and only one value for AppName. But that is not the case.
Diagnosing The Problem
To determine if there is more than one instance of AppName mapped to the same value of ReportNum, run this query:
Select reportnum, reportname, appname From report Where reportnum In (Select reportnum From report Group By reportnum Having count(*) > 1) Order By 1, 2;
If more than one row is returned from the query, then more than one report is mapped to the same value of ReportNum.
Resolving The Problem
If more than one row is returned from the query, then the solution is to modify the value of ReportNum for all but one of the rows, such that all values of ReportNum are now unique. Any values of ReportNum that you modify in table Report must be modified in table ReportLookup as well.
[{«Product»:{«code»:»SSLKT6″,»label»:»IBM Maximo Asset Management»},»Business Unit»:{«code»:»BU055″,»label»:»Cognitive Applications»},»Component»:»Upgrade Utility»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Version Independent;6.2.8;7.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}},{«Product»:{«code»:»SSKTXT»,»label»:»Tivoli Change and Configuration Management Database»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Not Applicable»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}},{«Product»:{«code»:»SS6HJK»,»label»:»Tivoli Service Request Manager»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Not Applicable»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}},{«Product»:{«code»:»SSLKTY»,»label»:»Maximo Asset Management for IT»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»Not Applicable»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]