Solved

OPENQUERY INNER JOIN on two Remote Tables

Posted on 2004-10-06
3
6,390 Views
Last Modified: 2008-01-09
I have two remote tables where I need have an INNER JOIN with OPENQUERY

This is what I want to do in regular SQL without OPENQUERY

SELECT * FROM hmodta.hmembp AS a
INNER JOIN mcnet..user_header AS p
ON a.SUBNO+a.PERNO = p.ExternalSystemKey
ORDER BY a.SUBNO DESC

Here is what I have for OPENQUERY.  The 2 remote servers are AS400 and WEBSRVR

SELECT * FROM
OpenQuery(AS400, 'SELECT * FROM hmodta.hmembp AS a INNER JOIN')
OpenQuery(WEBSRVR2, 'mcnet..user_header AS p ON a.SUBNO concat a.PERNO = p.ExternalSystemKey ORDER BY a.SUBNO DESC')

I know this isn't right, but could someone give me assistance?  Thanks in advance

0
Comment
Question by:vikingg97
[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
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
miron earned 500 total points
ID: 12242416
SELECT * FROM
OpenQuery(AS400, 'SELECT * FROM hmodta.hmembp')  as a INNER JOIN
OpenQuery(WEBSRVR2, 'mcnet..user_header')  AS p ON a.SUBNO concat a.PERNO = p.ExternalSystemKey ORDER BY a.SUBNO DESC
0
 
LVL 1

Author Comment

by:vikingg97
ID: 12242600
I needed to modify it a little to narrow the results, so here is what I did and the error I get. Do I need to do something different?

SELECT * FROM
OpenQuery(AS400, 'SELECT * FROM hmodta.hmembp')  as a INNER JOIN
OpenQuery(WEBSRVR2, 'mcnet..user_header')  AS p ON a.SUBNO + a.PERNO = p.External_System_Key  
WHERE a.cgrpid = 'S0038A'
ORDER BY a.SUBNO DESC

Error:
Server: Msg 2809, Level 18, State 1, Line 1
The request for procedure 'User_Header' failed because 'User_Header' is a table object.

0
 
LVL 1

Author Comment

by:vikingg97
ID: 12243083
Nevermind I figured it out. I needed to change it to this.  Also narrow down the fields.

Thanks for setting it up miron.

Solution:
SELECT * FROM
OpenQuery(AS400, 'SELECT SUBNO, PERNO, cgrpid FROM hmodta.hmembp') as a INNER JOIN
OpenQuery(WEBSRVR2, 'SELECT External_System_Key, User_ID FROM mcnet..user_header')  AS p ON a.SUBNO + a.PERNO = p.External_System_Key  
WHERE a.cgrpid = 'M00005'
ORDER BY a.SUBNO DESC
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 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