Tuesday, May 22, 2012

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

Everyday developers, testers and newbie’s face ORA-01427 error and I have seen many users looking for a solution for this error.

First let’s understand why users receive this error.

SQL> select count(*) from employee where employee_id = (select employee_id from salary where gross_sal < 5000 );
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

This error is raised when a sub-query returns more than one row.

Make sure you use multiple row sub query operator instead of single row sub-query operator to resolve the issue.

SQL> select count(*) from employee where employee_id in (select employee_id from salary where gross_sal < 5000 );

Now this query works fine.

Single row sub query operators are = (equal) , < (less than) , <=( less than or equal) , >(greater than) , >= (greater than or equal), <>(not equal ) ,!=(not equal)

Multiple row sub query operators are IN (equal to any member in the list), ANY (compare value to each value returned by the subquery), ALL (compare value to every value returned by the subquery)

Few guide lines for using Sub queries:
1. Use single-row operators with sing row sub queries
2. Use multiple-row operators with multiple row sub queries
3. Make sure you enclose sub queries in parentheses
4. Always keep sub queries on the right side of the comparison operator
5. You can use inline view to avoid ORA-01427 error, please refer http://docs.oracle.com/cd/E11882_01/server.112/e25554/qradv.htm#DWHSG08032

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com/

4 comments:

  1. I'm trying to to an update query that takes the timestamp from one column and updates another in the same table and I'm getting the single row subquery error.
    What am I doing wrong?

    UPDATE TRACKING_DATA_T T
    SET T.TD_TIMESTAMP_UTC = (
    SELECT T.TD_RUN_START_DTM +( I."TZ_OFFSET" / 24)
    FROM TRACKING_DATA_T T , IDS_LIST I
    WHERE
    T.TD_TRACKING_DATA_SITE = I.ZIP
    and T.td_last_updt_dtm Between To_Date ('2014/05/14 16:00:00','yyyy/mm/dd hh24:mi:ss')AND TO_DATE ('2014/05/14 16:15:00','yyyy/mm/dd hh24:mi:ss'));

    ReplyDelete
  2. I AM FACING 5THE SAME ERROR ORA-01427 SINGLE ROW SUBQUERY RETURNS MORE THAN ONE ROW KINDLY HELP ME.
    select e.div_id,ad.emp_id,e.emp_name,d.deptt_title,to_date(ad.in_date,'yyyymmdd') in_date,ad.in_time,ad.out_time,to_date(ad.out_date,'yyyymmdd') out_date,ad.attend_status,
    ad.month_status,/*,ot.ot_hours*/(select r.rem_title
    from attend_remarks r
    where r.trans_status='IN'
    and r.rem_id=ad.in_remarks
    )IN_REMARKS,
    (select r.rem_title
    from attend_remarks r
    where r.trans_status='OUT'
    and r.rem_id=ad.out_remarks)OUT_REMARKS
    from attendance_detail ad,employee e,department d---overtime_detail ot--,shleave_detail sh
    where e.comp_id=ad.comp_id
    and d.deptt_id=e.deptt_id
    --and ad.in_date between '20151101' and '20151115'
    and ad.emp_id=nvl(:Empid,ad.emp_id)
    --and ad.emp_id='151815'
    and (ad.in_remarks is not null or ad.out_remarks is not null)
    and ad.in_date between NVL(:indate1,ad.in_date) and NVL(:indate2,ad.in_date)
    and ad.deptt_id=nvl(:depttid,ad.deptt_id)
    --and ad.attend_status=Nvl(:PAttnSt,ad.attend_status)
    --and e.exit_flag=nvl(:P_exit_flag,e.exit_flag)
    order by ad.emp_id,deptt_title,ad.in_date

    ReplyDelete
  3. Hi ,
    Greetings from Application Plus Technologies !!
    We are providing training with certification for the below skill set ,
    *Oracle SQL
    * Advance SQL
    *PLSQL
    *Advance PLSQL
    * EBS Technical
    * Fusion technical
    If you are looking for training contact us
    WhatsApp No : 8108735227 / 7499992939
    Official Email : nida.k@applplus .com

    ReplyDelete
  4. After filing, if you owe taxes, pay before the deadline, or you will face penalties. You may also be owed a refund if you have overpaid your taxes. These could be paid directly or by check. Make sure to include bank account information when you are filing the form. Income Tax Return Filing

    ReplyDelete