Stack Overflow
Stack Overflow

Questions tagged [ora-00918]

ORA-00918: column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

34 questions
40
votes
4 answers

ORA-00918: column ambiguously defined in SELECT *

Getting ORA-00918: column ambiguously defined: running this SQL: SELECT * FROM (SELECT DISTINCT(coaches.id), people.*, users.*, coaches.* FROM "COACHES" INNER JOIN people ON people.id = coaches.person_id INNER JOIN users ON…
user43685
  • 1,456
  • 3
  • 17
  • 21
27
votes
10 answers

SQL: How to find duplicates based on two fields?

I have rows in an Oracle database table which should be unique for a combination of two fields but the unique constrain is not set up on the table so I need to find all rows which violate the constraint myself using SQL. Unfortunately my meager SQL…
James Adams
  • 8,448
  • 21
  • 89
  • 148
17
votes
5 answers

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

I've just come across a strange behaviour in Oracle where I would expect ORA-00918 to be raised, but isn't. Take this query, for example. SELECT * FROM USER_TABLES TAB JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME WHERE STATUS =…
batwad
  • 3,588
  • 1
  • 24
  • 38
8
votes
3 answers

Mixing "USING" and "ON" in Oracle ANSI join

I wrote an Oracle SQL expression like this: SELECT ... FROM mc_current_view a JOIN account_master am USING (account_no) JOIN account_master am_loan ON (am.account_no = am_loan.parent_account_no) JOIN ml_client_account mca USING (account_no) When I…
Sergey Stadnik
  • 3,100
  • 8
  • 27
  • 31
7
votes
5 answers

ORA-00918: column ambiguously defined: how to find the column

I'm getting the classic error: ORA-00918: column ambiguously defined Usually, I know how to solve it but my problem now is that I'm working with a 700 row query. Is there a way to identify the column?
Daniele
  • 71
  • 1
  • 2
  • 5
3
votes
2 answers

SQL "column ambiguously defined"- when run from hibernate

When I run a SQL query from sqldeveloper , it runs fine When I run the same query from Hibernate SQL session, it gives me: "ORA-00918: column ambiguously defined" error. Dies hibernate generate any kind of sql logs that can be used to debug the…
Victor
  • 16,609
  • 71
  • 229
  • 409
2
votes
2 answers

Hibernate Criteria causes ORA-00918: column ambiguously defined

I'm running the following code: Criteria crit = dao.getSesion().createCriteria(TAmbitos.class); crit.add( Restrictions.sqlRestriction("translate(upper(nombre), 'ÁÉÍÓÚ', 'AEIOU') like translate(upper(?),'ÁÉÍÓÚ', 'AEIOU')", …
Averroes
  • 4,168
  • 6
  • 50
  • 63
2
votes
3 answers

Oracle 12c - Ambiguous column in Insert Into Select Query, ORA-00918

I am trying to execute multiple insert with single statement to achieve this I am using Insert into select statement. But I am facing when two columns have same value in insert. Error message that I am getting is ORA-00918: column ambiguously…
Touheed Khan
  • 2,149
  • 16
  • 26
2
votes
0 answers

ORA-00918: column ambiguously defined when choose a list of paramlistpa

I know this question has been asked multiple times before, but I've seen every discussion about this here but none could help me to solve my problem. So my problem is I got this error. From what I understand is this error was caused by column could…
A. Ikhwan
  • 21
  • 2
1
vote
2 answers

FULL OUTER JOIN with two subselects gives "ORA-00918: column ambiguously defined"

both Subselects runs fine standalone. But I wish to join them togehter, which always gives "ORA-00918: column ambiguously defined". I don't see why? Both subelects uses other names. SELECT * FROM (SELECT aods.user_id, …
Sybil
  • 2,503
  • 3
  • 25
  • 36
1
vote
4 answers

Column ambiguously defined in subquery using rownums

I have to execute a SQL made from some users and show its results. An example SQL could be this: SELECT t1.*, t2.* FROM table1 t1, table2 t2, where table1.id = table2.id This SQL works fine as it is, but I need to manually add pagination and show…
Damntry
  • 35
  • 1
  • 7
1
vote
2 answers

ORA-00918: column ambiguously defined & ORA-00904 Invalid Identifier on SELECT query with Pivot

I'm fairly new to SQL, so forgive me if I'm not using correct terminology or missing something simple! I'm trying to create an output file with RC_ID on the left, and cost types across the top with cost amounts summed for each RC_ID/ cost type…
Savannah
  • 13
  • 2
1
vote
1 answer

Comparing values in two rows and getting ORA-00918: column ambiguously defined

I am trying to compare values from one column in two different rows using CASE statement. Inner SELECT statements are identical and working OK giving me fields that I need. Then I inner joined them on one of the key fields (KYCID). That's where I…
StephenV
  • 11
  • 2
1
vote
2 answers

How to fix this ORA-00918 error in this SQL code

I have ORA-00918 error with my code and i could not find the problem... the following code gives me this error. ORA-00918 : column ambiguously defined can anyone give me some advice? thanks SELECT * FROM ( SELECT * FROM ( SELECT ROWNUM AS RNUM,…
Jung Young-min
  • 111
  • 7
1
vote
4 answers

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

I'm creating a package in Oracle, and when I've compiled the body of the package, i am getting the PL/SQL: ORA-00918: column ambiguously defined error. I've gone through the code, and double checked the aliases, so am a bit stumped as to why I am…
KCB226
  • 13
  • 4
1
2 3