Solved

column ambiguously defined error when convert oracle to sql

Posted on 2010-08-30
12
660 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:kdeutsch
  • 5
  • 5
  • 2
12 Comments
 
LVL 2

Expert Comment

by:Subramanyeswara
ID: 33561995
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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33562050
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
 
LVL 2

Expert Comment

by:Subramanyeswara
ID: 33562103
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:kdeutsch
ID: 33562117
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
 

Author Comment

by:kdeutsch
ID: 33562127
Subramanyeswara,
Tried your version but got the same error
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33562132
this is your oracle query. kindly paste your sql query.
0
 

Author Comment

by:kdeutsch
ID: 33562135
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
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33562161
the problem is for the DMOS field. kindly add from which table for that field
0
 
LVL 10

Accepted Solution

by:
Jini Jose earned 350 total points
ID: 33562174
where      DMOS = ''25B1''')

is this field is from which table ?
0
 

Author Comment

by:kdeutsch
ID: 33562205
gmailjini:


aahaahahahahahaahahahah UGH!
Thanks
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33562210
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
 

Author Closing Comment

by:kdeutsch
ID: 33562229
THanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question