troubleshooting Question

Selecting data from 2 different schema

Avatar of kevinjd
kevinjd asked on
Oracle Database
17 Comments2 Solutions1123 ViewsLast Modified:
Hi,

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)

select is_trans_type,invoice_number,invoice_date
from tablefin.sales_order_invoices
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.
Kevin
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros