Column Ambiguously Defined

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.

How to solve 'ORA-00918: column ambiguously defined'

you should define columns belongs which table

select B.TITTLE , O.NAME, L.ADDRESS 
    from BOOK B , BORROWER O, BOOK_LOANS L  
    where B.BOOKID= L.BOOKID and L.BRANCHID = 
        (select BRANCHID from LIBRARY_BRANCH where BRANCHNAME='Branch_C')
        and (O.CRADNO=L.CRADNO) and L.DUE_DATE='2013-2-13'  

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.

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

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

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.

Column Ambiguously Defined - No Line / Column Number

looks like you have a duplicate definition of i_utc_offset (see code snippet below)

(select v.value_id as i_value_id
 , v.value_nb as i_value_nb
 , v.utc_offset as i_utc_offset
 , v.data_Date as i_data_date
 , v.hr_utc as i_hr_utc
 , v.utc_offset as i_utc_offset

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.

---

Related Topics