How do you join OpenQuery onto another Select statement?-CodePudding

I have written statement using OpenQuery and now want to join it onto my another query, so that when I run the view it returns a table with records from both selects. How would I go about this?

These are the queries I currently have:

SELECT  I.Invoice_ID,
        I.Invoice_Num,
        I.SO_ID,
        I.SO_Num, 
        O.AccountNumber, 
        I.ShipZip               AS PostCode,
        I.RequestedDeliveryDate AS DeliveryDate,
        I.AddDate,
        I.AddUser,
        O.OrgName,
        I.[Status]
    FROM dbo.Org O  
    INNER JOIN dbo.Invoice I ON O.Org_ID = I.Org_ID

(SELECT * FROM OPENQUERY ([TEST-SQL-TEST-02], 'SELECT GoodsOutTest, LoadRef FROM TestWMS.dbo.GoodsTest')

CodePudding user response：

You could store the results of the openquery in a CTE or temp table. Just expect the performance to be awful with large tables.

e.g.

WITH GoodsOut AS (
    SELECT * FROM OPENQUERY (
        [AKW-SQL-TEST-02], 'SELECT GoodsOutNumber, LoadRef FROM DeltaWMS.dbo.GoodsOut'
    )
)

SELECT I.Invoice_Num,
I.SO_ID,
I.SO_Num, 
O.AccountNumber, 
I.ShipZip               AS PostCode,
I.RequestedDeliveryDate AS DeliveryDate,
I.AddDate,
I.AddUser,
O.OrgName,
I.[Status]
FROM dbo.Org O  
INNER JOIN dbo.Invoice I ON O.Org_ID = I.Org_ID
INNER JOIN GoodsOut ON --whatever

CodePudding user response：

Why not just literally JOIN to it?

SELECT I.Invoice_ID,
       I.Invoice_Num,
       I.SO_ID,
       I.SO_Num,
       O.AccountNumber,
       I.ShipZip AS PostCode,
       I.RequestedDeliveryDate AS DeliveryDate,
       I.AddDate,
       I.AddUser,
       O.OrgName,
       I.[Status],
       GT.GoodsOutTest
FROM dbo.Org O
     INNER JOIN dbo.Invoice I ON O.Org_ID = I.Org_ID
     INNER JOIN OPENQUERY ([TEST-SQL-TEST-02], 'SELECT GoodsOutTest, LoadRef FROM TestWMS.dbo.GoodsTest') GT ON GT.LoadRef = I.Ref; --Guess ON clause, correct to be appropriate for your data.