SQL Server Performance

Using Join in a Distributed Query !!!!!!

Discussion in 'General Developer Questions' started by rwaldron, Sep 5, 2005.

  1. rwaldron New Member

    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
    DataDataData
    DataDataData


    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.

Share This Page