Ambiguous Column Name Error

[veerranu]

MERGE INTO stbl_pcop_dafmaster_av x
using
( select rsn,mdn,sdca,sdca,sdca,sdca,sdca,sdca,sdca,sdca,sd ca,sdca,sdca,sdca,sdca,sdca,sdca,sdca,sdca,sdca,sd ca,sdca,sdca,sdca,sdca,sdca
from stbl_phonegen_new where MDN in
(select a.mdnnumber from stbl_pcop_dafmaster_av a
where a.cafnumber in (select b.cafnumber from stbl_dafxml_export_summary b where repush_flag='1' and pg_status='1' and pg_msg ='MDN RSN Mismatch') )
)
y on (x.mdnnumber = y.mdn)
when matched then
update set x.rsnnumber = y.rsn
WHEN NOT MATCHED THEN
INSERT
VALUES ('0','0','0','0','0','0','0','0','0','0','0','0',' 0','0','0','0','0','0','0','0','0','0','0','0','0' ,'0')




WHILE EXECUTING THIS STATEMENT I M GETTING ERROR LIKE


The following error has occurred:

ORA-00918: column ambiguously defined

PLEASE HELP



I NEED TO UPDATE RSNNUMBER FIELD OF MY DAFMASTER TABLE FOR THOSE ROWS THAT HAVE MDNNUMBER SAME AS MDN OF PHONEGEN TABLE WITH THE CONDITIONS AS DEFINED ( I AM RUNNING IT USING TOAD TOOL)


PLEASE GUIDE ME ON THE SAME

[Stang02GT]

please refer to this post i answered a question about ambiguous error i think it will help you.

[veerranu]

still i m stuck into the same hole.
someone please pull me out,
Stang02GT your solution was of no help to me plz suggest an alternative

[Stang02GT]

still i m stuck into the same hole.
someone please pull me out,
Stang02GT your solution was of no help to me plz suggest an alternative

are you sure you don't have two column names that are the same?

[veerranu]

are you sure you don't have two column names that are the same?

At last the problem is solved.
There was a problem with the sdca columns,i got them changed to sdca+1,sdca+2....and so on.
thanks for the support

[Stang02GT]

At last the problem is solved.
There was a problem with the sdca columns,i got them changed to sdca+1,sdca+2....and so on.
thanks for the support

So you did have to columns with the same name?

[veerranu]

So you did have to columns with the same name?

It was a bit different, yes there were more than one column with same name in the same select statement ;however that wasnt a problem itself.actually when i was executing the select statement independently.it was giving me the output, but once i put that into merge statement,it posed problems,like the one i mentioned above.

i feel while comparing the rows from the two tables executor was in a fix to determine which column to compare to what column in other table but why?? i dont know..

i mean it could easily have chosen the columns in order of their declaration

if you could throw some light into the problem...?

[Stang02GT]

It was a bit different, yes there were more than one column with same name in the same select statement ;however that wasnt a problem itself.actually when i was executing the select statement independently.it was giving me the output, but once i put that into merge statement,it posed problems,like the one i mentioned above.

i feel while comparing the rows from the two tables executor was in a fix to determine which column to compare to what column in other table but why?? i dont know..

i mean it could easily have chosen the columns in order of their declaration

if you could throw some light into the problem...?

Yes but with SQL you have to be very specific. So in this case where you had the two columns that were of the same name, it doesn't know which one to take, and there is no default like you are talking about where it just takes the 1st one declared. SQL (like many other languages) needs precise step by step instructions.


Think of it like this. If there were two balls (exactly the same) laying on the floor and you told someone "get that ball and return it to me". How are they going to know which ball to get. But if you say get me the ball on the right or get my the ball on the left then the person knows exactly which ball you are talking about.