Solved

column ambiguously defined error when convert oracle to sql

Posted on 2010-08-30
12
664 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

710 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