Solved

column ambiguously defined error when convert oracle to sql

Posted on 2010-08-30
12
661 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server query 18 42
Need return values from a stored procedure 8 21
SQL - Simple Pivot query 8 15
Need to find substring in SQL 5 15
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to recover a database from a user managed backup
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.

821 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