Correct syntax for using inner join with pagination in Oracle

Question

I have the following query:

select * from 
  (select rownum rnum, p.* from 
    (select DEPARTMENTS.DEPARTMENT_ID, 
     DEPARTMENTS.DEPARTMENT_NAME, 
     DEPARTMENTS.MANAGER_ID,
     EMPLOYEES.EMPLOYEE_ID, 
     EMPLOYEES.FIRST_NAME, 
     EMPLOYEES.LAST_NAME, 
     EMPLOYEES.MANAGER_ID, 
     EMPLOYEES.DEPARTMENT_ID, 
     EMPLOYEES.DETAILS 
     from HR.EMPLOYEES 
     INNER JOIN HR.DEPARTMENTS on
     HR.DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID 
     where EMPLOYEES.EMPLOYEE_ID >= 1) p 
  where rownum <= 4) 
where rnum >= 2

that gives me the following error:

Error SQL: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined"

If the fields have different names there's no errors. As an example, this query is correct:

select * from 
   (select rownum rnum, p.* from 
     (select DEPARTMENTS.DEPARTMENT_ID, 
       DEPARTMENTS.DEPARTMENT_NAME,
       DEPARTMENTS.MANAGER_ID,
       EMPLOYEES.EMPLOYEE_ID, 
       EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       EMPLOYEES.DETAILS 
       from HR.EMPLOYEES
       INNER JOIN HR.DEPARTMENTS on
       HR.DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID 
     where EMPLOYEES.EMPLOYEE_ID >= 1) p
   where rownum <= 4) 
where rnum >= 2

What is the correct syntax to make an inner join with pagination, and fields from different tables with the same names? How can I avoid the ORA-00918 error in my first query?

I'm using Oracle 11g.

stefan stefan · Accepted Answer · 2018-11-07T08:12:46

Use column aliases in the inner query eg

  -- outer query omitted, table aliases and column aliases added
  select rownum rnum, p.* 
  from (
    select 
      D.DEPARTMENT_ID     did
    , D.DEPARTMENT_NAME   dname
    , D.MANAGER_ID        dmgr
    , E.EMPLOYEE_ID       eid
    , E.FIRST_NAME        efname
    , E.LAST_NAME         elname
    , E.MANAGER_ID        emgr
    , E.DEPARTMENT_ID     edept
    -- , E.DETAILS 
    from HR.EMPLOYEES E
      INNER JOIN HR.DEPARTMENTS D on D.DEPARTMENT_ID = E.DEPARTMENT_ID 
    where E.EMPLOYEE_ID >= 1 ) p
;

-- result
RNUM  DID  DNAME            DMGR  EID  EFNAME     ELNAME       EMGR  EDEPT  
1     10   Administration   200   200  Jennifer   Whalen       101   10     
2     20   Marketing        201   201  Michael    Hartstein    100   20     
3     20   Marketing        201   202  Pat        Fay          201   20     
4     30   Purchasing       114   114  Den        Raphaely     100   30     
5     30   Purchasing       114   115  Alexander  Khoo         114   30     
6     30   Purchasing       114   116  Shelli     Baida        114   30     
7     30   Purchasing       114   117  Sigal      Tobias       114   30     
8     30   Purchasing       114   118  Guy        Himuro       114   30     
9     30   Purchasing       114   119  Karen      Colmenares   114   30   
...

The MANAGER_ID and DEPARTMENT_ID columns (without aliases) are causing the error you have described (remove the comments to get the error message):

  select rownum rnum, p.* 
  from (
    select 
      D.DEPARTMENT_ID
    , D.DEPARTMENT_NAME
    --, D.MANAGER_ID
    , E.EMPLOYEE_ID
    , E.FIRST_NAME
    , E.LAST_NAME
    , E.MANAGER_ID
    --, E.DEPARTMENT_ID
    from HR.EMPLOYEES E
      INNER JOIN HR.DEPARTMENTS D on D.DEPARTMENT_ID = E.DEPARTMENT_ID 
    where E.EMPLOYEE_ID >= 1 ) p
;
-- ORA-00918: column ambiguously defined

Correct syntax for using inner join with pagination in Oracle

1 Answers