Why Doesn't Oracle Raise "Ora-00918: Column Ambiguously Defined" for This Query

Why doesn't Oracle raise ORA-00918: column ambiguously defined for this query?

Can't say when it was fixed, but here's my results:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> SELECT *
  2  FROM USER_TABLES TAB
  3  JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
  4  WHERE STATUS = 'DISABLED';
WHERE STATUS = 'DISABLED'
      *
ERROR at line 4:
ORA-00918: column ambiguously defined

SQL> ed
Wrote file afiedt.buf

  1  SELECT *
  2  FROM USER_TABLES TAB
  3  JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME
  4  JOIN USER_CONSTRAINTS CON ON CON.TABLE_NAME = TAB.TABLE_NAME
  5* WHERE STATUS = 'DISABLED'
SQL> /
WHERE STATUS = 'DISABLED'
      *
ERROR at line 5:
ORA-00918: column ambiguously defined

How to fix PL/SQL: ORA-00918: column ambiguously defined in Oracle

Your function parameter name and the name of the field are clashing, creating a shadowing effect. You can prefix the name of the parameter with the function name to remove the ambiguity

 AND LA.UIO_ID = MyfunctionName.UIO_ID;

Alternatively, rename the parameter to avoid such occurrences.

How to fix ORA-00918: column ambiguously defined

Most likely a cut and paste error but only select a column once or you have to alias the second to distinguish it from the first.

       t1.DEP_CARRIER,
       t1.DEP_MODE,
       t1.DEP_CARRIER,    -- duplicate column name in cte1

Pivot Issue ORA-00918 column ambiguously defined

You probably need something like this

select * from tab
    PIVOT
    (
    MAX(Value)
    FOR LABEL
    IN ('Plan/UnPlanned' as PU,
        'Reason for' as R,
        'Name & "ID"' as NI)
 )  

    Number    SUMMARY D     STATUS         PU          R         NI
---------- ---------- - ---------- ---------- ---------- ----------
         2          2 z          2                                1
         1          1 x          1          2                      
         2          2 y          2                     1

I.e. 1) add alias to you pivot labels

2. do not multiplicate the MAX calculation, it it the responsibility of PIVOT to calculate max for each label.

ORA-00918: column ambiguously defined in SELECT *

A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting * your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.

The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID.

Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT * is regarded as bad practice in production code: explicitly named columns are much safer.

ORA-00918: Column ambiguously defined when inserting multiple rows

The issue here is that you haven't given the columns in your placeholder query any names. Try:

INSERT INTO guest (first_name, last_name, address, phone, email,
                   document_id, nationality, status, reservation_id, document_type_id) 
  WITH names (first_name, last_name, address, phone, email, 
              document_id, nationality, status, reservation_id, document_type_id) AS ( 
    SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1, 1, 1 FROM dual UNION ALL 
    SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1, 2, 1 FROM dual UNION ALL 
    SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1, 3, 1 FROM dual UNION ALL 
    SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1, 4, 1 FROM dual 
  )
  SELECT * FROM names;

When you don't specify any names, Oracle names the columns after the values in the first row: 'JOHN', 'DOE' etc. This causes ambiguity for you because you have 3 columns containing a value 1 in the first row, resulting in 3 columns named 1!

This query illustrates how Oracle is naming the columns:

  WITH names  AS ( 
    SELECT 'John', 'Doe', 'Grove Street 8', 111222333, 'johndoe@mail.com', 'JFV5R3', 'English', 1 FROM dual UNION ALL 
    SELECT 'Jane', 'Done', 'Sunrise Avenue 10', 111222335, 'janed@mail.com', 'GFV433', 'English', 1 FROM dual UNION ALL 
    SELECT 'Hannah', 'Drewton', 'Elm Street 8', 551222333, 'hannah@mail.com', 'GETER3', 'English', 1 FROM dual UNION ALL 
    SELECT 'David', 'Drewton', 'Elm Street 8', 551225333, 'dvddrw@mail.com', '94TER3', 'English', 1 FROM dual 
  )
  SELECT * FROM names where "'DOE'" = 'Drewton';

Results:

'JOHN' 'DOE'   'GROVESTREET8'     111222333 'JOHNDOE@MAIL.CO 'JFV5R 'ENGLIS          1
------ ------- ----------------- ---------- ---------------- ------ ------- ----------
Hannah Drewton Elm Street 8       551222333 hannah@mail.com  GETER3 English          1
David  Drewton Elm Street 8       551225333 dvddrw@mail.com  94TER3 English          1

Oracle SQL column ambiguously defined with `FETCH FIRST n ROWS ONLY`

This is documented in oracle documents:

Restrictions on the row_limiting_clause

If the select list contains columns with identical names and you
specify the row_limiting_clause, then an ORA-00918 error occurs. This
error occurs whether the identically named columns are in the same
table or in different tables. You can work around this issue by
specifying unique column aliases for the identically named columns.

Even though SELECT query works, after using FETCH FIRST|NEXT, it will throw error if two of the column names are same.

You should just assign different alias names for all columns in SELECT clause.

---

Related Topics