Avatar of kalees
kalees
Flag for United Kingdom of Great Britain and Northern Ireland 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
Oracle Database

Avatar of undefined
Last Comment
kalees

8/22/2022 - Mon
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)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
kalees

ASKER
Many thanks again  slightwv, sorry for the dud information.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy