ORA-00918: column ambiguously defined when creating the Expanded Report

In this opportunity, a student of the Oracle APEX course had this error, and as it is very possible that the same thing happens to others, I have decided to share here the solution.

I made a demo, in which I am using the same data model of the course, but it applies to any situation.

I'm using Oracle APEX version 19.2.

The definition of my Enrollment table is:

When creating a Master Detail - Drill Down page, both pages are created fine, but when I want to edit a master page record by clicking on the pencil icon, the following error is displayed, "ORA-00918: ambiguously defined column".

Reviewing the debugging of the page I can see that APEX indicates that the statement generating the error is:

- error_statement: select i.*
 from (
select "MAT_ID","ALU_ID","FECHA","USUARIO","OBSERVACIONES","TOTAL",(lead("MAT_ID",1)over(order by null)) "NEXT_1",(lag("MAT_ID",1)over(order by null)) "PREV_1",(row_number()over(order by null)) "ROWN",(count(*)over()) "TOTAL"
from ((
select /*+ qb_name(apex$inner) */d."MAT_ID",d."ALU_ID",d."FECHA",d."USUARIO",d."OBSERVACIONES",d."TOTAL" from (
select x.* from "EDU_MATRICULAS" x 

) d

 )) i 

) i where 1=1 


 and "MAT_ID"=:apex$f1

Analyzing the SQL statement, my EDU_MATRICULAS table has a column called TOTAL, and when creating the master-detail of type Drill Down or Expanded as shown in Spanish, it generates three additional columns, one of which is (count(*)over()) “TOTAL” which, as we can see, is also called TOTAL.

There are two solutions, one would be to rename the column of the table from TOTAL to TOTAL_MAT, but that implies changes at database level and if we are already using the column in other pages it can generate problems and errors.

Note: It is better, after this, not to create columns in our table that are only called TOTAL, but something more concrete, since APEX uses that name to create the record count.

So to solve this, what we can do is to give an ALIAS name to our TOTAL column.

To do this we go to the detail page, select the Form type region, and then in Source instead of showing the EDU_MATRICULAS table, we will select SQL Query:

All the columns of the table will be displayed:

select MAT_ID,
       ALU_ID,
       FECHA,
       TOTAL,
       USUARIO,
       OBSERVACIONES
  from EDU_MATRICULAS

We place an alias to the "TOTAL" column as "TOTAL_MAT".

We save the changes, go back to the master page and click on the pencil icon and now we can edit the registry without any problems.

I hope it will be useful!

See you next time!