column ambiguously defined in oracle apex?

Question

I have use this code as an interactive report sql query & toad and receive the same error, the idea is simply to join two tables :

select request.* from ( select distinct
  a.REQUEST_ID,
 (select full_name from  apps.per_all_people_f ppf where sysdate between effective_start_date 
 and effective_end_date and a.EMPLOYEE_NUMBER in (ppf.national_identifier,ppf.employee_number) 
 and current_employee_flag = 'Y' )   EMPLOYEE_NUMBER,
 a.DEPARTMENT,
 a.LOCATION,
 a.PROBLEM_TYPE,
 a.PROBLEM_PRIORITY,
 a.PROBLEM_SUMMARY,
 a.PROBLEM_DATE ,
 a.CREATED_BY ,
 a.CREATION_DATE,
 a.LAST_UPDATE_DATE,
 a.LAST_UPDATE_LOGIN,
 a.LAST_UPDATED_BY,
 a.STATUS,
 a.CLOSED_DATE,
 a.ASSIGNED_TO,
 a.EXTENSION,
 'Assign' as Assign,
 (select user_name from apps.fnd_user where user_id=a.created_by) Assigned_by       ,
 c.REQUEST_ID,
 c.FILENAME,
 c.MIME_TYPE,
 c.BLOB_CONTENT                
from IT_REQUESTS  a, IT_REQUESTS_ATTACH c
where 1=1
and a.request_source = 'IT'
and a.status                 = 'CLOSED'--'NEW'
and a.request_id = c.request_id
order by a.PROBLEM_DATE desc) request

error received: ORA-20999: Failed to parse SQL query! ORA-06550: line 10, column 4: ORA-00918: column ambiguously defined

Answer 1

Remove one of the duplicate Request_id columns (since you already have the join condition a.request_id = c.request_id then they must always be the same value). You can also remove the unnecessary 1=1 filter and the SELECT wrapping the main query:

SELECT DISTINCT
       a.REQUEST_ID,
       ( SELECT full_name
         FROM   apps.per_all_people_f ppf
         WHERE  sysdate BETWEEN effective_start_date 
                        AND     effective_end_date
         AND    a.EMPLOYEE_NUMBER in (ppf.national_identifier,ppf.employee_number) 
         AND    current_employee_flag = 'Y'
       ) AS  EMPLOYEE_NUMBER,
       a.DEPARTMENT,
       a.LOCATION,
       a.PROBLEM_TYPE,
       a.PROBLEM_PRIORITY,
       a.PROBLEM_SUMMARY,
       a.PROBLEM_DATE ,
       a.CREATED_BY ,
       a.CREATION_DATE,
       a.LAST_UPDATE_DATE,
       a.LAST_UPDATE_LOGIN,
       a.LAST_UPDATED_BY,
       a.STATUS,
       a.CLOSED_DATE,
       a.ASSIGNED_TO,
       a.EXTENSION,
       'Assign' as Assign,
       ( SELECT user_name
         FROM   apps.fnd_user
         WHERE  user_id=a.created_by
       ) AS Assigned_by,
       c.FILENAME,
       c.MIME_TYPE,
       c.BLOB_CONTENT                
FROM   IT_REQUESTS a
       INNER JOIN IT_REQUESTS_ATTACH c
       ON (a.request_id = c.request_id)
WHERE  a.request_source = 'IT'
AND    a.status         = 'CLOSED'--'NEW'
ORDER BY a.PROBLEM_DATE desc

Answer 2

To illustrate what you did, I'll use Scott's sample schema.

This is a simple join between two tables; both of them contain DEPTNO column (as indicated) - no problem so far:

SQL> select e.deptno,   --> here
  2         e.ename,
  3         e.job,
  4         d.deptno,   --> and here
  5         d.dname
  6  from emp e join dept d on e.deptno = d.deptno
  7  where d.deptno = 10;

    DEPTNO ENAME      JOB           DEPTNO DNAME
---------- ---------- --------- ---------- --------------
        10 CLARK      MANAGER           10 ACCOUNTING
        10 KING       PRESIDENT         10 ACCOUNTING
        10 MILLER     CLERK             10 ACCOUNTING

This is your situation: you used such a query as a subquery (named it request). This won't work any more because - which DEPTNO is which?

SQL> select request.*
  2  from (select e.deptno,   --> here
  3               e.ename,
  4               e.job,
  5               d.deptno,   --> and here
  6               d.dname
  7        from emp e join dept d on e.deptno = d.deptno
  8        where d.deptno = 10
  9       ) request;
select request.*
               *
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL>

---

What to do? As Mat commented, one option is to use only one of those columns, they have the same value anyway. Another one is to use column aliases:

SQL> select request.*
  2  from (select e.deptno as emp_deptno,    --> here
  3               e.ename,
  4               e.job,
  5               d.deptno as dept_deptno,   --> and here
  6               d.dname
  7        from emp e join dept d on e.deptno = d.deptno
  8        where d.deptno = 10
  9       ) request;

EMP_DEPTNO ENAME      JOB       DEPT_DEPTNO DNAME
---------- ---------- --------- ----------- --------------
        10 CLARK      MANAGER            10 ACCOUNTING
        10 KING       PRESIDENT          10 ACCOUNTING
        10 MILLER     CLERK              10 ACCOUNTING

SQL>

---

Generally speaking, it is a bad habit to use asterisk while working with columns. Always name all of them; yes, it requires some more typing, but makes things much more clear. Therefore, avoid select request.* and specify select emp_deptno, ename, job, dept_deptno, dname from request instead.