0

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?

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

  • 1
    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. – ypercubeᵀᴹ Feb 19 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.