Thanks for the question, Sean.
Asked: May 25, 2017 - 2:58 pm UTC
Last updated: May 26, 2017 - 2:24 am UTC
Version: Oracle 12.1
Viewed 1000+ times
You Asked
I have a list of accounts that users want me to check if they got updated in my table in a refresh or not. Is there a way to check for those records in a list that were NOT inserted after a certain date? I just need the ones not inserted:
SELECT * FROM ICAP where cap_refresh_dt >= '24-MAY-2017'
and cap_css_acct_no in
(
'462001120118087',
'511121871600033',
'511121877101069',
'511121893100137',
'511121896600026',
'511121905600017',
'511121911600118' ... [I have +1000 accounts in the list]
);
If I say NOT IN, I may get records that are on the table PRIOR to 5/24/2017.
Thanks!
and Connor said...
You can put the account numbers into a table, and then it can just be a simple outer join.
For example: "Emps in table T versus those in scott.emp"
SQL> select * from t;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
1234
5678
7423
5432
18 rows selected.
SQL>
SQL> select t.empno,
2 e.empno
3 from t,
4 scott.emp e
5 where t.empno = e.empno(+)
6 order by 1;
EMPNO EMPNO
---------- ----------
1234
5432
5678
7369 7369
7423
7499 7499
7521 7521
7566 7566
7654 7654
7698 7698
7782 7782
7788 7788
7839 7839
7844 7844
7876 7876
7900 7900
7902 7902
7934 7934
18 rows selected.
SQL>
SQL> select t.empno,
2 e.empno,
3 case when e.empno is null then 'Was not inserted' end msg
4 from t,
5 scott.emp e
6 where t.empno = e.empno(+)
7 order by 1;
EMPNO EMPNO MSG
---------- ---------- ----------------
1234 Was not inserted
5432 Was not inserted
5678 Was not inserted
7369 7369
7423 Was not inserted
7499 7499
7521 7521
7566 7566
7654 7654
7698 7698
7782 7782
7788 7788
7839 7839
7844 7844
7876 7876
7900 7900
7902 7902
7934 7934
18 rows selected.