September 5, 2005 at 10:40 am
Hi all
I am currently Migrating alot of Access reports to SQL..
I have a linked server in SQL created to an IBM Database B2
So I am converting the SQL created with access into Distributed queries in SQL
All has gone well until I have a join of 2 tables that I need to do from WITHIN a Distributed Query.
Eg: The following works perfectly
Linked server is Movex_MVXAMODSFC
PIMEIG is a table within the linked server
SELECT Z2CONO AS 'Company', Z2WZIS AS 'TAC', Z2TX40 AS 'Desc'
FROM OPENQUERY(MOVEX_MVXAMODSFC, 'Select * from MVXAMODSFC.PIMEIG ')WHERE Z2CONO=200
The statemt above Returns ok and I get
Company Tac Desc
Data Data Data
Data Data Data
But if I try this below....
Linked Server Movex_MVXAMODSFC
MITMAS is a table within the linked server
MITBAL is a Table within the linked server
I am trying to do a join on these 2 tables where
The field MITMAS.MMCONO = MITBAL.MBCONO and MITMAS.MMITNO = MITBAL.MBITNO
I'm not sure how to go about this on a distributed query as
you only get to do 1 SELECT Stament From an OPENQUERY as above.
Does Anyone know how to do this ??
I have tried the folowing but I know the overall approach is incorrect.
SELECT MMSTAT AS Status, MMITNO AS [Item Number], Sum(MITBAL.MBSTQT) AS [In Stock], Sum(MITBAL.MBAVAL) AS [Allocable on hand Balance]
FROM OPENQUERY(MOVEX_MVXAMODSFC MVXADTA_MITMAS LEFT JOIN MVXADTA_MITBAL ON (MVXADTA_MITMAS.MMCONO = MVXADTA_MITBAL.MBCONO) AND (MVXADTA_MITMAS.MMITNO = MVXADTA_MITBAL.MBITNO)
Thanks im Advance,
Ray.
September 8, 2005 at 8:00 am
This was removed by the editor as SPAM
September 8, 2005 at 8:24 am
You can do the join on the remote server (including any filters that you want to apply to the data set) and get back only the data that you need. Example:
SELECT *
FROM OPENQUERY (WM_WIN_SP2_LS, 'SELECT A.USER_ID, B.LOGIN_USER_ID FROM USER_MASTER A, USER_ROLE B WHERE A.LOGIN_USER_ID = B.LOGIN_USER_ID AND B.ROLE_ID = ''*''')
Where WM_WIN_SP2_LS is the name of the linked server and the remote passthrough query joins the USER_MASTER and the USRE_ROLE table on the remote machine and applies a filter as well.
Another alternative is to do two passthrough queries and then do joins on the result sets that you get from those, example:
SELECT A.USER_ID, B.LOGIN_USER_ID
FROM
(SELECT * FROM OPENQUERY (WM_WIN_SP2_LS, 'SELECT * FROM USER_MASTER')) A
INNER JOIN
(SELECT * FROM OPENQUERY (WM_WIN_SP2_LS, 'SELECT * FROM USER_ROLE WHERE ROLE_ID = ''*''')) B
ON A.LOGIN_USER_ID = B.LOGIN_USER_ID
Solution 1 is much better - the processing is shifted to the remote server and you get only what you need.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply