troubleshooting Question

Selecting data from 2 different schema

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

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,, 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.
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