Missing data in SQL Server 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?
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