CODEWITHSUNDEEP
oracle-database
user3461160
user3461160

Reputation: 1

ORA-00918: column ambiguously defined..How do i resolve this error?

Here is the query:

select   
           t1_c1,
           case 
           when max(t1_c2) = 'X'
           then  max(t2_c3) 
           else 'X ' || max(t2_c3) 
           end as t2_c3
from    
           t1 a1,
           t2
where  t1_c4 =
(
select  max(a2.t1_c4) 
from   t1 a2 
where a1.t1_c1 = a2.t1_c1 
and    a2.t1_c4 <= '31-AUG-'||&ws_acad_yr
)
and    t1_c2 = t2_c8(+)
group by t1_c1
)

where  c1 = t3_c1
and    t3_c5 is null
and    c1 = t4_c1
and    t4_c6 = t5_c8(+)
and    t4_c5 = t6_c8(+)
and    c1 = t7_c1(+)
and    c1 = t8_c1(+)
and    c1 = t1_c1(+)

I am using pidm column from two or more table..but i am not sure how to resolve this? I am not sure how and where to prefix the column or not. I am a new user to this.

Upvotes: 0

Views: 278

Answers (1)

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146460

When you need to distinguish between two columns that have the same name you can provide a prefix with the table it belongs to:

SELECT line.total, invoice.total
FROM invoice
INNER JOIN line ON invoice.invoice_id=line.invoice_id

But that's not always possible/desirable:

  • It isn't possible it the column comes from a subquery
  • It isn't useful if you read the same table twice
  • It isn't practical if table name is too long

To solve this, SQL allows to define aliases. You are already using them!

from t1 a1
     ^  ^
     |   \ Alias
     \ Table

ORA-00918: column ambiguously defined means that you have a column name that belongs to more than one column and Oracle doesn't know which one you mean. The complete error message possibly tells you the column name and the exact position where you are using it. Add the appropriate table/alias prefix and you'll be done.

Upvotes: 2

Related Questions