Forum Stats

  • 3,770,488 Users
  • 2,253,125 Discussions
  • 7,875,484 Comments

Discussions

how to get data from first table(not exists) when tables reside in different DB.

User_DIL60
User_DIL60 Member Posts: 20 Green Ribbon

 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.

Answers