column ambiguously defined error when convert oracle to sql

Trying to convert an Oracle to sql OPENQUERY and it should work when I bracket it but I get tons or errors when i tried to do this so i am just rewriting.

Here is my next error on the join of the 2 tables.  Teh differencet is on the RSC column in ACN.ACN_CURRENT_SLOTS it has 7 letters versus just the 6 letters in ACN.ACN_BONUS_PERCENT_TLKP  but I get this error when trying to connect on it

column ambiguously defined so here is the Oracle query part
FROM      ACN_ACN_CURRENT_SLOTS INNER JOIN
            ACN_ACN_BONUS_PERCENT_TLKP ON (Left([ACN_ACN_CURRENT_SLOTS].[RSC],6) = ACN_ACN_BONUS_PERCENT_TLKP.RSC6) AND
            (ACN_ACN_CURRENT_SLOTS.DMOS = ACN_ACN_BONUS_PERCENT_TLKP.DMOS)


Here is my sql equivalent
from	ACN.ACN_CURRENT_SLOTS a INNER JOIN			ACN.ACN_BONUS_PERCENT_TLKP b ON (LEFT(a.RSC, 6) = b.RSC6) and a.DMOS = b.DMOS

I also tried this
from	ACN.ACN_CURRENT_SLOTS a INNER JOIN			ACN.ACN_BONUS_PERCENT_TLKP b ON Substring(a.RSC,0, 6) = b.RSC6 and a.DMOS = b.DMOS

Open in new window

kdeutschAsked:
Who is Participating?
 
Jini JoseSenior .Net DeveloperCommented:
where      DMOS = ''25B1''')

is this field is from which table ?
0
 
SubramanyeswaraCommented:
Try this below query:
from    ACN.ACN_CURRENT_SLOTS a INNER JOIN            ACN.ACN_BONUS_PERCENT_TLKP b ON Substr(a.RSC,0, 6) = b.RSC6 and a.DMOS = b.DMOS

Open in new window

0
 
Jini JoseSenior .Net DeveloperCommented:
can u please put you select query here ?

you need to give the alias name in front of every field name if the field is repeating in more than one table in that select query
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SubramanyeswaraCommented:
Ignore my previous message.try  this  below query


from    ACN.ACN_CURRENT_SLOTS a INNER JOIN            ACN.ACN_BONUS_PERCENT_TLKP b ON (Substring(a.RSC,0, 6) = b.RSC6) and a.DMOS = b.DMOS
0
 
kdeutschAuthor Commented:
Here is my whole select query,
Select * from OPENQUERY(SIDPERS,
                        'Select a.UPC,
                                    a.ADDR_CITY,
                                    a.POSN_TITLE,
                                    a.PARA,
                                    a.LINE,
                                    a.RANK,
                                    a.DMOS,
                                    a.GENDER,
                                    a.AUTH,
                                    a.OS,  
                                    a.ASGN,
                                    a.ACN,
                                    b.B_PERC,
                                    a.AUTH + a.OS - a.ASGN - nvl(a.ACN, 0) as VAC,
                                    a.UNIT_TYPE
                        from    ACN.ACN_CURRENT_SLOTS a INNER JOIN            
                                    ACN.ACN_BONUS_PERCENT_TLKP b ON Substr(a.RSC,0, 6) = b.RSC6 and a.DMOS = b.DMOS
                        where      DMOS = ''25B1''')
0
 
kdeutschAuthor Commented:
Subramanyeswara,
Tried your version but got the same error
0
 
Jini JoseSenior .Net DeveloperCommented:
this is your oracle query. kindly paste your sql query.
0
 
kdeutschAuthor Commented:
all,
I am told the oracle query version works fine here is the whole oracle query.
SELECT      ACN_ACN_CURRENT_SLOTS.RSC,
            ACN_ACN_CURRENT_SLOTS.UPC,
            ACN_ACN_CURRENT_SLOTS.POSN_TITLE,
            ACN_ACN_CURRENT_SLOTS.DMOS,
            ACN_ACN_CURRENT_SLOTS.AUTH,
            ACN_ACN_CURRENT_SLOTS.OS,
            ACN_ACN_CURRENT_SLOTS.ASGN,
            ACN_ACN_CURRENT_SLOTS.ACN,
            IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]) AS VAC,
            ACN_ACN_BONUS_PERCENT_TLKP.B_PERC
FROM      ACN_ACN_CURRENT_SLOTS INNER JOIN
            ACN_ACN_BONUS_PERCENT_TLKP ON (Left([ACN_ACN_CURRENT_SLOTS].[RSC],6) = ACN_ACN_BONUS_PERCENT_TLKP.RSC6) AND
            (ACN_ACN_CURRENT_SLOTS.DMOS = ACN_ACN_BONUS_PERCENT_TLKP.DMOS)
WHERE      (((IIf(IsNull([ACN]),[AUTH]+[OS]-[ASGN],[AUTH]+[OS]-[ASGN]-[ACN]))>0) AND
            ((ACN_ACN_CURRENT_SLOTS.OESTS)="N") AND
            ((ACN_ACN_CURRENT_SLOTS.CRIT_BONUS) Is Null) AND
            ((ACN_ACN_CURRENT_SLOTS.MOB) Is Null))
ORDER BY ACN_ACN_CURRENT_SLOTS.RSC, ACN_ACN_CURRENT_SLOTS.UPC, ACN_ACN_CURRENT_SLOTS.PARA,
                  ACN_ACN_CURRENT_SLOTS.LINE; ====================
0
 
Jini JoseSenior .Net DeveloperCommented:
the problem is for the DMOS field. kindly add from which table for that field
0
 
kdeutschAuthor Commented:
gmailjini:


aahaahahahahahaahahahah UGH!
Thanks
0
 
Jini JoseSenior .Net DeveloperCommented:
column ambiguously defined error means any of the fields defined in select, update, delete, where etc is referring two tables. so you have to give the exact table name or alias name
0
 
kdeutschAuthor Commented:
THanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.