Friday, November 29, 2013

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>

No comments: