asked on

Need to solve an Oracle Query 3 tables (Joins)

I am very new to Oracle and SQL and I have the following scenario which I need some assistance in solving.

Tables

AR_Invoice
AR_inv_no GL_Code AR_Amount AR_Date
1234 11500 500.00 01/01/2015
2345 11500 300.00 01/02/2015
3456 11501 250.00 03/01/2015
etc

AP_Invoice
AP_Inv_No GL_Code AP_Amount AP_Date
4567 12500 350.00 02/05/2015
5678 12500 275.00 03/04/2015
6789 12501 150.00 01/03/2015
etc

Tran_Doc_No
Inv_No GL_Code Reg_No Type
1234 11500 ABC 122 M
1234 11500 ABC 122 F
1234 11500 ABC 122 R
2345 11500 ABC 124 F
3456 11501 ABC 121 M
3456 11501 ABC 121 F
3456 11501 ABC 121 R
4567 12500 ABC 122 M
5678 12500 ABC 124 M
5678 12500 ABC 124 R
6789 12501 ABC 127 M

Most of the records in the Tran_Doc_No have multiple lines, there is much more info which does differ line by line but the only info I need from this table is the Reg_No

The only GL_Codes that I need in the query are 11500 and 12500

I have looked to I need to achieve the following

Reg_No AR_Inv_No AR_Amount AR_Date AP_Inv_No AP_Amount AP_Date
ABC 122 1234 500.00 01/01/2015 4567 350.00 02/05/2015
ABC 124 2345 300.00 01/02/2015 5678 275.00 03/04/2015

I do hope that I have explained this accurate enough, if not please ask.

Kind regards,

Keith

slightwv (䄆 Netminder)

Try this:

/*
drop table myAR_Invoice purge;
create table myAR_Invoice( AR_inv_no number, GL_Code number,  AR_Amount number,  AR_Date date);

insert into myAR_Invoice values(1234,11500,500.00,to_date('01/01/2015','MM/DD/YYYY'));
insert into myAR_Invoice values(2345,11500,300.00,to_date('01/02/2015','MM/DD/YYYY'));
insert into myAR_Invoice values(3456,11501,250.00,to_date('03/01/2015','MM/DD/YYYY'));
commit;

drop table myAP_Invoice purge;
create table myAP_Invoice (AP_Inv_No number, GL_Code number, AP_Amount number, AP_Date date);
insert into myAP_Invoice values(4567,12500,350.00,to_date('02/05/2015','MM/DD/YYYY'));
insert into myAP_Invoice values(5678,12500,275.00,to_date('03/04/2015','MM/DD/YYYY'));
insert into myAP_Invoice values(6789,12501,150.00,to_date('01/03/2015','MM/DD/YYYY'));
commit;


drop table myTran_Doc_No purge;
create table myTran_Doc_No(Inv_No number, GL_Code number, Reg_No char(7), Type char(1));
insert into myTran_Doc_No values(1234,11500,'ABC 122','M');
insert into myTran_Doc_No values(1234,11500,'ABC 122','F');
insert into myTran_Doc_No values(1234,11500,'ABC 122','R');
insert into myTran_Doc_No values(2345,11500,'ABC 124','F');
insert into myTran_Doc_No values(3456,11501,'ABC 121','M');
insert into myTran_Doc_No values(3456,11501,'ABC 121','F');
insert into myTran_Doc_No values(3456,11501,'ABC 121','R');
insert into myTran_Doc_No values(4567,12500,'ABC 122','M');
insert into myTran_Doc_No values(5678,12500,'ABC 124','M');
insert into myTran_Doc_No values(5678,12500,'ABC 124','R');
insert into myTran_Doc_No values(6789,12501,'ABC 127','M');
commit;
*/


select t.reg_no,
	max(r.ar_inv_no), max(r.ar_amount), max(r.ar_date),
	max(p.ap_inv_no), max(p.ap_amount), max(p.ap_date)
from (
		select distinct inv_no, gl_code, reg_no from myTran_Doc_No
	) t
	left outer join myAR_Invoice r on t.inv_no = r.ar_inv_no and t.gl_code = r.gl_code
	left outer join myAP_Invoice p on t.inv_no = p.ap_inv_no and t.gl_code = p.gl_code
where t.gl_code in (11500, 12500)
group by t.reg_no
/

Open in new window

kalees

ASKER

slightwv, you have come to my rescue again, however, I have provided bad information!!!!

Because I am mocking up the scenario due to data protection, I have give slightly incorrect information, the date sits in Tran_Doc_No, not AP_INVOICE and AR_INVOICE.

I have commented out the date and amended names and it works perfect.

AR_Invoice
AR_inv_no GL_Code AR_Amount
1234 11500 500.00
2345 11500 300.00
3456 11501 250.00
etc

AP_Invoice
AP_Inv_No GL_Code AP_Amount
4567 12500 350.00
5678 12500 275.00
6789 12501 150.00
etc

Tran_Doc_No
Inv_No GL_Code Reg_No Type DATE
1234 11500 ABC 122 M 01/01/2015
1234 11500 ABC 122 F 01/01/2015
1234 11500 ABC 122 R 01/01/2015
2345 11500 ABC 124 F 01/02/2015
3456 11501 ABC 121 M 03/01/2015
3456 11501 ABC 121 F 03/01/2015
3456 11501 ABC 121 R 03/01/2015
4567 12500 ABC 122 M 02/05/2015
5678 12500 ABC 124 M 03/04/2015
5678 12500 ABC 124 R 03/04/2015
6789 12501 ABC 127 M 01/03/2015

I have attempted to amend your code without success.

Could you again please assist?

Again, appplogies for the dud info

Keith

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kalees

ASKER

Many thanks again slightwv, sorry for the dud information.