ORA-01427

This was tested on Oracle 11.2. I created a small table as shown below:

 

SQL> select * from emp

  2  order by emp_name, year

  3  /

 

EMP_NAME   YEAR     SALARY

---------- ---- ----------

ANGUS      2000      15000

ANGUS      2001      16000

BORIS      2000      15000

BORIS      2001      15500

COLIN      2000      15500

COLIN      2001      16000

 

6 rows selected.

 

SQL>

 

I wanted to see who earned the same as Angus each year and thought I could do it like this. It failed with an ORA-01427 as the subquery returned two rows, not one, and Oracle did not know which one to compare against:

 

SQL> select *

  2  from emp

  3  where emp_name != 'ANGUS'

  4  and salary =

  5  (select salary from emp

  6   where emp_name = 'ANGUS')

  7  order by emp_name, year

  8  /

(select salary from emp

*

ERROR at line 5:

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

row

 

SQL>

 

I changed the SQL to use a correlated subquery instead. This compared the results year by year and, as Angus only had one salary each year, it ran successfully:

 

SQL> select *

  2  from emp x

  3  where emp_name != 'ANGUS'

  4  and salary =

  5  (select salary from emp

  6   where emp_name = 'ANGUS'

  7   and year = x.year)

  8  order by emp_name, year

  9  /

 

EMP_NAME   YEAR     SALARY

---------- ---- ----------

BORIS      2000      15000

COLIN      2001      16000

 

SQL>