I have 2 sql statements, each producing a list of invoice numbers. Ideally, I'd like to create a SQL statement that will produce JUST the numbers that exist in recordset 1, and NOT in recordset 2. In other words, a list that will show the differences between the 2 recordsets. The tables are also in different schemas.
The first recordset is produced by the following statement (These are in "TEST1" tablespace)
where sys_edi_status_code = 'N' and is_trans_type <> ' ';
The second list is produced via the following statement: (These tables are in "TEST2" tablespace)
select transaction0.document_number, trading_partner.name, transaction0.FA_ACCEPT_SW
from transaction0, trading_partner where
transaction0.log_field2 = 'V' and
transaction0.second_doc_number = trading_partner.applic_partner_cod and
to_char(transaction0.document_date, 'mmddyy') = to_char(sysdate, 'mmddyy');
Is it possible to join the 2, selecting only those in the first recordset that AREN'T in the second set.
Thanks in advance.
There are a few ways to skin this cat: you can use subqueries (in several ways). Something like:
from schema1.table where some_column not in (select column from schema2.table where...)
or use the minus operator:
select column from schema1.table
select column from schema2.table;
Please repost if this does not get you going (like what are the common fields between the 2 tables/schemas etc...).