Link to home
Create AccountLog in
Avatar of kevinjd
kevinjd

asked on

Selecting data from 2 different schema

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

As long as the executing schema can see the tables in the other schema it is possible.

There are a few ways to skin this cat:  you can use subqueries (in several ways).  Something like:
select.....
from schema1.table where some_column not in (select column from schema2.table where...)

or use the minus operator:

select column from schema1.table
minus
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...).
Avatar of kevinjd

ASKER

I understand what you're saying, but I'm having trouble putting it together with my statements...

Ok, using the following 2 select statements, the first statement comes from the "FINPROD" schema, and the second select statement comes from the "EDIUSER" schema.  The common fields between the 2 are "invoice_number" and "document_number", respectively.  These are the fields i want to see the differences between.

select is_trans_type,invoice_number,invoice_date
from finprod.sales_order_invoices
where sys_edi_status_code = 'N' and is_trans_type <> '  ';

select unique transaction0.document_number Invoice, trading_partner.name, transaction0.FA_ACCEPT_SW Ack
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') order by trading_partner.name;
Based on the original question: "Is it possible to join the 2, selecting only those in the first recordset that AREN'T in the second set.
"

It should go something like (I can't test this it's just an example):

select  is_trans_type,invoice_number,invoice_date
from finprod.sales_order_invoices
where sys_edi_status_code = 'N' and is_trans_type <> '  ' and invoice_number not in (
  select select unique transaction0.document_number 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') order by trading_partner.name
);

Performance can come into play and there might be more efficient ways of doing this.

for example:

select ...
from ...
where invoice_number in (
     select invoice_number from finprod.sales_order_invoices where ...
     minus
     select select unique transaction0.document_number from from transaction0, trading_partner where ...
);


select is_trans_type,invoice_number,invoice_date
from tablefin.sales_order_invoices
where sys_edi_status_code = 'N' and is_trans_type <> '  '
minus
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');
You don't have to join them, the UNION does it all for you,

From the first select, every record found in second select will be substracted.
>> peterside7
Note: To use minus in this way, ALL the columns from the first must exist and be the same datatype in the second.
I just looked at both select you're doing :

For the union to work, both select have to return column in same order (to compare)

So select 1 :
select is_trans_type,invoice_number,invoice_date

and
select 2:
select transaction0.document_number, trading_partner.name, transaction0.FA_ACCEPT_SW

You compare is_trans_type  with   transaction0.document_number
                   invoice_number  with  trading_partner.name
                   invoice_date  with transaction0.FA_ACCEPT_SW

I guess this will not work.
try to select  is_trans_type , invoice_number ,invoice_date  from 2nd select and you will get what you want.

I think that if you need more data from the 2 select, then post your table and provide the keys joining them.



slightwv ,
YEP ... with his question , I assumed that the 2 selects were built like that, but you posted that while I was writing my next post. I looked at both select, and I saw a date to be compared with a FA_ACCEPT_SW.  
I BIG red alert the flashed in my eyes   :-))
TRY THIS :

select finprod.is_trans_type,finprod.invoice_number,finprod.invoice_date,transaction0.document_number Invoice, trading_partner.name, transaction0.FA_ACCEPT_SW Ack
from finprod.sales_order_invoices, transaction0, trading_partner
where finprod.sys_edi_status_code = 'N' and finprod.is_trans_type <> '  '
and 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') order by trading_partner.name
and finprod.invoice_number in (select invoice_number from sales_order_invoices minus
                               select transaction0.document_number from transaction0);
FINAL, one join between  finprod.invoice_number and transaction0.document_number  was missing:


select finprod.is_trans_type,finprod.invoice_number,finprod.invoice_date,transaction0.document_number Invoice, trading_partner.name, transaction0.FA_ACCEPT_SW Ack
from finprod.sales_order_invoices, transaction0, trading_partner
where finprod.sys_edi_status_code = 'N' and finprod.is_trans_type <> '  '
and 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') order by trading_partner.name
and finprod.invoice_number in (select invoice_number from sales_order_invoices minus
                               select transaction0.document_number from transaction0)
and finprod.invoice_number = transaction0.document_number;
Avatar of kevinjd

ASKER

Ok, I ran this last statement from peterside7..

SQL> select finprod.is_trans_type,finprod.invoice_number,finprod.invoice_date,transaction0.document_number Invoice, trading_partnerk
  2  from finprod.sales_order_invoices, transaction0, trading_partner
  3  where finprod.sys_edi_status_code = 'N' and finprod.is_trans_type <> '  '
  4  and transaction0.log_field2 = 'V' and
  5  transaction0.second_doc_number = trading_partner.applic_partner_cod
  6  and to_char(transaction0.document_date, 'mmddyy') = to_char(sysdate, 'mmddyy') order by trading_partner.name
  7  and finprod.invoice_number in (select invoice_number from sales_order_invoices minus
  8                                 select transaction0.document_number from transaction0)
  9  and finprod.invoice_number = transaction0.document_number;
and finprod.invoice_number in (select invoice_number from sales_order_invoices minus
*
ERROR at line 7:
ORA-00933: SQL command not properly ended
Avatar of kevinjd

ASKER

Also, I ran what peterside7 suggested above...with some additions.  I had to insert a to_char in a few of the columns so the datatypes would match up.
It looks to me as if its ignoring whatever the "minus" is supposed to do, and running the 2 select statements seperately...with seperate result sets...

Here is what was returned...

SQL> select is_trans_type, to_char(invoice_number) inv, to_char(invoice_date)
  2  from finprod.sales_order_invoices
  3  where to_char(invoice_date, 'mmddyy') = to_char(sysdate-1, 'mmddyy') and
  4  is_trans_type <> '  ' order by is_trans_type;
minus
select trading_partner.name, to_char(transaction0.document_number) inv_num, 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_TRANS_T INV        TO_CHAR(I
---------- ---------- ---------
APT880     276255     17-JAN-05
APT880     276256     17-JAN-05
AWG880     276262     17-JAN-05
AWG880     276263     17-JAN-05
AWG880     276306     17-JAN-05
AWG880     276265     17-JAN-05
AWG880     276285     17-JAN-05
COS810     276253     17-JAN-05
COS810     276302     17-JAN-05
COS810     276303     17-JAN-05
CS880      276236     17-JAN-05

IS_TRANS_T INV        TO_CHAR(I
---------- ---------- ---------
KM810      276269     17-JAN-05
NAF880     276315     17-JAN-05
PUB880     276304     17-JAN-05
PUB880     276305     17-JAN-05
RA810      276257     17-JAN-05
RA810      276267     17-JAN-05
RA810      276266     17-JAN-05
SAF880     276237     17-JAN-05
SAF880     276238     17-JAN-05
TG810      276270     17-JAN-05
TG810      276271     17-JAN-05

IS_TRANS_T INV        TO_CHAR(I
---------- ---------- ---------
WF810      276258     17-JAN-05
WF810      276259     17-JAN-05
WF810      276268     17-JAN-05
WF810      276261     17-JAN-05
WIN880     276284     17-JAN-05
WM810      276290     17-JAN-05
WM810      276291     17-JAN-05
WM810      276292     17-JAN-05
WM810      276293     17-JAN-05
WM810      276294     17-JAN-05
WM810      276295     17-JAN-05

IS_TRANS_T INV        TO_CHAR(I
---------- ---------- ---------
WM810      276296     17-JAN-05
WM810      276297     17-JAN-05
WM810      276298     17-JAN-05
WM810      276299     17-JAN-05
WM810      276300     17-JAN-05
WM810      276301     17-JAN-05

39 rows selected.

SQL> SP2-0042: unknown command "minus" - rest of line ignored.
SQL>   2    3    4    5  
NAME                 INV_NU FA_
-------------------- ------ ---
WAL MART             275991
WAL MART             275991 A


ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Also,

get rid of the ';'  (the selects around the minus are all 1 single statement)
            V
...s_type;
minus
remove the order by , it was at the wrong location in the select

order by trading_partner.name
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Did you try the sql from the last post ?
Do you have what you are looking for ?
Regards..