Joins? with Linked Server Tables

I am moving transactional data from our ERP (SAP) database to SQL Server (2K)for reporting purposes.  The idea is to keep the reporting load off the transactional system.  I am tring to keep the master data in SAP so I don't have to replicate all tables, just the big ones). I have set up a Linked server to the Oracel data.

I have a SQL Server (2K) table "custhist" with columns "custno" and "totalsales".

I have a linked server (Oracle/SAP) on the same server with table "custmaster" with columns "custno" and "custname" index on custno.

What is the best performing syntax to return all records from custhist for custno = "123" containing columns custno, custname and totalsales ?











sperry4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiverGuyCommented:
I worked with other linnked systems to SQL Server, but not Oracle.

You could do a pass-through query as:
SELECT * FROM OpenQuery(LinkedOracleServer, 'SELECT blah, blah')


Or, you could query it on SQL Server:
SELECT * FROM OracleServer.Database.Table


The syntax might have to be messed with.

You could also look into Microsoft Analysis Services if you did want to move some of the data over in the future for data mining.  It takes a bit of time to capture, massage and save the data, but the response time for browsing cubes and the like is almost instantaneous in some situations.
0
sperry4Author Commented:
looking for the exact syntax of the select statement that would join the SQL server table data to the linked server table data.

0
arbertCommented:
Like this--SQL Usually whines on 3part database names, so make sure you put a table alias on your linked oracle database in the from statement:


select * from custhist as SQLTable
inner join OracleServer.Database.Table as OracleTable
on  sqltable.custno=oracletable.custno
 and sqltable.totalsales=oracletable.totsales


Brett
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sperry4Author Commented:
Sorry if I'm turning this into a new question but I believe the example query given will run slow (with out using index?).

I believe this to be true as when I run Query 1 below, it takes minutes versus the seconds Query 2 takes.  There is an ORACLE index on MANDT+VRGAR+RBLEN.

Is there a way to use OPENQUERY() in the example query given by arbert on 4/1 ?

Query 1
Select * from SAPLINKED..SAPR3.CE1MEDA AS X where X.MANDT='100' and X.VRGAR='F' and X.RBELN = '1014580858'

Query 2
Select * From OPENQUERY(SAPLINKED, "SELECT * from SAPR3.CE1MEDA where SAPR3.CE1MEDA.MANDT='100' and SAPR3.CE1MEDA.VRGAR='F' and SAPR3.CE1MEDA.RBELN = '1014580858'")



0
ispalenyCommented:
Query 2 is run at server side, but there are some changes in SP3.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.