Using Join in a Distributed Query !!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using Join in a Distributed Query !!!!

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-09-05 : 12:24:47

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.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-05 : 15:12:55

OPENQUERY(MOVEX_MVXAMODSFC, 'select * from MVXADTA_MITMAS LEFT JOIN MVXADTA_MITBAL ON (MVXADTA_MITMAS.MMCONO = MVXADTA_MITBAL.MBCONO) AND (MVXADTA_MITMAS.MMITNO = MVXADTA_MITBAL.MBITNO')
Openquery just takes the linked server name then sends the string following to be executed on it.

don't know what syntax the server takes but it is better to transfer as little as possible
OPENQUERY(MOVEX_MVXAMODSFC, 'select MMSTAT AS Status, MMITNO AS [Item Number], Sum(MITBAL.MBSTQT) AS [In Stock], Sum(MITBAL.MBAVAL) AS [Allocable on hand Balance]
from MVXADTA_MITMAS LEFT JOIN MVXADTA_MITBAL ON (MVXADTA_MITMAS.MMCONO = MVXADTA_MITBAL.MBCONO) AND (MVXADTA_MITMAS.MMITNO = MVXADTA_MITBAL.MBITNO grouop by MMSTAT, MMITNO ')

In your first query Z2CONO=200 should be inside the remote query if possible - as you have it it would transfer the data to your server then do the filter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-09-06 : 10:47:23

Thx for the reply...
I use your advice and got the following to work..
Th elast where had to be outside the openquery else I got and error.
thx for your help

SELECT *
FROM OPENQUERY(MOVEX_MVXADTA, 'SELECT MITMAS.MMSTAT AS Status, MITMAS.MMITNO AS Item_Number, Sum(MITBAL.MBSTQT) AS In_Stock, Sum(MITBAL.MBAVAL) AS Allocable_on_hand_Balance
FROM MITMAS LEFT JOIN MITBAL ON (MITMAS.MMCONO = MITBAL.MBCONO) AND (MITMAS.MMITNO = MITBAL.MBITNO)
WHERE MITMAS.MMCONO=200
GROUP BY MITMAS.MMSTAT, MITMAS.MMITNO ')WHERE STATUS = 20
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-06 : 17:24:01

try

SELECT *
FROM OPENQUERY(MOVEX_MVXADTA, 'SELECT MITMAS.MMSTAT AS Status, MITMAS.MMITNO AS Item_Number, Sum(MITBAL.MBSTQT) AS In_Stock, Sum(MITBAL.MBAVAL) AS Allocable_on_hand_Balance
FROM MITMAS LEFT JOIN MITBAL ON (MITMAS.MMCONO = MITBAL.MBCONO) AND (MITMAS.MMITNO = MITBAL.MBITNO)
WHERE MITMAS.MMCONO=200
GROUP BY MITMAS.MMSTAT, MITMAS.MMITNO ') a WHERE STATUS = 20

but it is better to include the where clause inside the openquery statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-09-07 : 10:39:06

Hiya,
And thx for your help so far..
Th elast statement I got away with having the where outside the openquery but as you said it is quicker if I can get the where statement within the string that get sent to the linked server..
Can you help with this one...

Linked server to Pervasive DB ( BTRIEVE)
The following works from SQL but takes 40 Seconds
SELECT *
From Openquery(sbase_live_link, 'select * FROM STDCHRGE ') WHERE Unit_Type= 'HANDSET'

The following works when run directly on the Pervasive server ..takes 5 seconds

SELECT *
From STDCHRGE WHERE (Unit_Type= 'HANDSET')

But if I try and put the where inside the openquery to speed things up ie:

SELECT *
From Openquery(sbase_live_link, 'select * FROM STDCHRGE WHERE Unit_Type= 'HANDSET' ')

I get the error
Line 2: Incorrect syntax near 'HANDSET'.

I also tried No ' ' Quotations and got the below

SELECT *
From Openquery(sbase_live_link, 'select * FROM STDCHRGE WHERE Unit_Type= HANDSET ')

[ODBC Engine Interface]Error in expression: HANDSET ]
[OLE/DB provider returned message: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Invalid column name: 'HANDSET'.]

Any ideas ?
Ray

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-07 : 17:07:23

SELECT *
From Openquery(sbase_live_link, 'select * FROM STDCHRGE WHERE Unit_Type= ''HANDSET''')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-09-08 : 04:50:45

Yep,
Thx for that.
''Handset'' does the trick.
So It looks like the column name needs to be wrapped in another set of single quotes when sending as a distributed query.
'' as apposed to "

Thx again,
Ray.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources