• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7876
  • Last Modified:

OPENQUERY INNER JOIN on two Remote Tables

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
vikingg97
Asked:
vikingg97
  • 2
1 Solution
 
mironCommented:
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
 
vikingg97Author Commented:
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
 
vikingg97Author Commented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now