Missing data in SQL Server

Question

I need to find the data that is present in Oracle, but missing from SQL Server.

I have tried the below query, but am getting the following error:

The multi-part identifier "PS.TXN_ID" could not be bound.

SELECT TXN_ID 
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ')
WHERE NOT EXISTS (SELECT TXN_ID 
 FROM PA_TRANSACTIONS PT
 WHERE PT.TXN_ID = PS.TXN_ID)

Can someone help out please?

Answer 1

You have not aliased the OPENQUERY result set as "PS" in your supplied query. Without the alias "PS", SQL Server does not know to what this refers, so PS.TXN_ID is meaningless.

Try this:

SELECT TXN_ID 
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ') ps
WHERE NOT EXISTS (select TXN_ID from PA_TRANSACTIONS PT WHERE PT.TXN_ID =PS.TXN_ID)

Alternatively, try using the EXCEPT operator:

SELECT TXN_ID 
FROM OPENQUERY(PEX,'SELECT TXN_ID FROM PEX.PEX_SALE_TRAN_CUBE ') 
EXCEPT 
SELECT TXN_ID FROM PA_TRANSACTIONS

EXCEPT Operator

• ypercubeᵀᴹ: You can add that there is a slight difference between the two solutions. If the openquery subquery returns some duplicate TXN_ID values, the 1st solution will show them all. The 2nd will apply distinct and remove the duplicates.