I have a requirement to identify the records in history table that doesn't exist in stage table? The caveat is that I need to use the file date from the stage table to use as deactivation date and update history table. For example STAGE has cols name, IDand date as abc, 123, 01/01/2021 and history has xyz, 234, 12/31/2999 and abc, 123, 12/31/2999. Since xyz,234 doesn't exist in stage, this record needs to be terminated in history table with 01/01/2021. sample output will be xyz, 234, 01/01/2021. Can anyone help me with the query?
INSERT INTO STAGE ( NAME, ID,FILE_DT) VALUES ('ABC','123', '01/01/2021');
INSERT INTO HISTORY ( NAME, ID,FILE_DT) VALUES ('ABC','123', '12/31/2999');
INSERT INTO HISTORY ( NAME, ID,FILE_DT) VALUES ('XYZ','234', '12/31/2999');
SELECT KEY, (SELECT FILE_DT FROM STAGE where rownum=1 ) AS FILE_DT FROM
HISTORY H WHERE NOT EXISTS
(SELECT HPC_NUM, NAME FROM STAGE S WHERE H.HPC_NUM =S.HPC_CODE AND H.NAME=S.NAME )
This query seems to be working when both the tables are in same database. I am not sure how to achieve this when we have tables in different databases with no DB links.