Home » SQL & PL/SQL » SQL & PL/SQL » SQL Join
SQL Join [message #276501] Thu, 25 October 2007 06:21 Go to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Hi,

I am trying to join 2 tables (Orders and Invoices), to create a single summary table. Appologies for not formatting the data correctly.

Orders
Ord No Ord Qty Ord Value
1111 200 2000.00


Invoices
Ord No Inv No Inv Qty Inv Value
1001 2001 100 1000.00
1001 2002 72 720.00

Required Table
Ord No Inv No Ord Qty Ord Value Inv_Qty Inv_Value
1001 2001 100 1000.00 100 1000.00
1001 2002 72 720.00 72 720.00
1001 (null) 28 280.00 (null) (null)

Thanks in advance
Cheers
Milford.
Re: SQL Join [message #276511 is a reply to message #276501] Thu, 25 October 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To know how to format you can read OraFAQ Forum Guide, section "How to format your post?".

Can you explain with words how you result to your final chart from the 2 tables.
If you want a faster and more accurate answer, post the create table and insert statements for a test case.
Also post your Oracle version (with 4 decimals).

Regards
Michel

[Updated on: Thu, 25 October 2007 06:51]

Report message to a moderator

Re: SQL Join [message #276528 is a reply to message #276501] Thu, 25 October 2007 07:17 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
milford_f

Appologies for not formatting the data correctly.

So you knew in advance that you won't format data you displayed? Being a member since 2006, what prevented you to read the OraFAQ Forum Guide?

I don't know about the others, but I wouldn't accept such an apology as the reason behind is pure disrespect toward other Forum members.
Re: SQL Join [message #276533 is a reply to message #276501] Thu, 25 October 2007 07:46 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I have tried to understand what your requirements are, but it's not easy. I'm not sure where the ORDERS table comes into things, since it doesn't appear on the output. As far as I can tell, you want to subtract the two values (ord qty & ord value) in the invoice table where rows have the same order no. Several questions then arise:

a) How are you identifying which of the two rows is to be subtracted ? Is it always the one with the lower value being subtracted from the higher value ? Or is it the one with the higher invoice number being subtracted ?

b) What happens if there are three or more rows ? Is one of them chosen as the positive amount and all the others then subtracted from it ? Or do you never get more than two rows ?
Re: SQL Join [message #276550 is a reply to message #276501] Thu, 25 October 2007 08:23 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Thanks for your response, I am trying to generate a report to measure what orders have been invoiced within the required date.

My problem is that due to non-availability of items, the goods are sometimes shipped in two or more lots. When i create another table by joining the 2, the order quantity automatically gets duplicated again.

Hope this helps, i have managed to put the code below.

Cheers
Milford.


CREATE TABLE ord
(SNO_REQUIRED_DATE		DATE,
SNO_ORD_NO		NUMBER(8,0),
SNO_LINE_NO		NUMBER(4,0),
SNO_ITEMCODE		VARCHAR2(10),
SNO_ORD_QTY		NUMBER(12,2),
SNO_ORD_VAL		NUMBER(12,2));
commit;

insert into ord
select '13-OCT-07', 1111, 1, 7311, 200, 2000.00
from dual;
commit;

drop table inv;

CREATE TABLE inv
 (SNI_INVOICE_DATE	DATE,
SNI_INV_NO		NUMBER(8,0),
SNI_ORD_NO		NUMBER(8,0),
SNI_LINE_NO		NUMBER(4,0),
SNI_ITEMCODE		VARCHAR2(10),
SNI_INV_QTY		NUMBER(12,2),
SNI_INV_VAL		NUMBER(12,2));
commit;

insert into inv
select '27-SEP-07', 2222, 1111, 1, 7311, 100, 1000.00
from dual;
insert into inv
select '13-OCT-07', 2223, 1111, 1, 7311, 72, 720.00
from dual;
commit; 

drop table cs1;

CREATE TABLE cs1
(SN_REQUIRED_DATE		DATE,
SN_INVOICE_DATE		DATE,
SN_ORD_NO		NUMBER(8,0),
SN_INV_NO		NUMBER(8,0),
SN_ITEMCODE		VARCHAR2(10),
SN_ORD_QTY		NUMBER(12,2),
SN_ORD_VAL		NUMBER(12,2),
SN_INV_QTY		NUMBER(12,2),
SN_INV_VAL		NUMBER(12,2));
commit;

insert into cs1
select sno_required_date, sni_invoice_date, 
sno_ord_no, sni_inv_no, sno_itemcode, sno_ord_qty, 
sum(sno_ord_val), sum(sni_inv_qty), sum(sni_inv_val)
from ord, inv
where sno_ord_tracer = sni_ord_tracer(+)
and sno_line_no = sni_line_no(+)
group by sno_req_date, sni_con_inv_date, sno_ord_ref, 
sno_ord_tracer, sni_inv_tracer, sno_itemcode, sno_ord_qty;
commit;

[Updated on: Thu, 25 October 2007 08:35] by Moderator

Report message to a moderator

Re: SQL Join [message #276553 is a reply to message #276550] Thu, 25 October 2007 08:35 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
milford_f wrote on Thu, 25 October 2007 09:23


CREATE TABLE ord
(SNO_REQUIRED_DATE		DATE,
SNO_ORD_NO		NUMBER(8,0),
SNO_LINE_NO		NUMBER(4,0),
SNO_ITEMCODE		VARCHAR2(10),
SNO_ORD_QTY		NUMBER(12,2),
SNO_ORD_VAL		NUMBER(12,2));
commit;

insert into ord
select '13-OCT-07', 1111, 1, 7311, 200, 2000.00
from dual;



This is not the correct way to insert a DATE value into a DATE column. You need to use a TO_DATE function.
Re: SQL Join [message #276562 is a reply to message #276501] Thu, 25 October 2007 08:48 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The SQL you've got doesn't work. The nearest I could get to something that actually worked was:

insert into cs1 
select sno_required_date, sni_invoice_date, sno_ord_no, 
sni_inv_no, sno_itemcode, sno_ord_qty, sum(sno_ord_val),
 sum(sni_inv_qty), sum(sni_inv_val) 
from ord, inv 
where sno_ord_no = sni_ord_no(+) 
and sno_line_no = sni_line_no(+) 
group by sno_required_date, sni_invoice_date, sno_ord_no,
 sni_inv_no, sno_itemcode, sno_ord_qty


Why are you trying to make the ORD_QTY exactly the same as the INV_QTY ? You seem just to be ending up with a report which has two identical sets of columns.

[Updated on: Thu, 25 October 2007 08:58] by Moderator

Report message to a moderator

Re: SQL Join [message #276565 is a reply to message #276501] Thu, 25 October 2007 09:00 Go to previous messageGo to next message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Could you suggest a better way

I thought that i would create one table and then put two conditiond that
if order qty - invoice qty = 0,
and if invoiced date <= required date
then order delivered in full on time
else order not delivered in time

Re: SQL Join [message #276577 is a reply to message #276501] Thu, 25 October 2007 09:41 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Try this:

select sno_ord_no, SNI_INV_NO, SNI_inv_QTY, sni_inv_val, sni_inv_qty, sni_inv_val
from ord, inv
where sno_ord_no = sni_ord_no(+)
and sno_line_no = sni_line_no(+)
union all
select sno_ord_no, null, sno_ord_qty - sum(sni_inv_qty) , sno_ord_val - sum (sni_inv_val), NULL, NULL
from ord, inv
where sno_ord_no = sni_ord_no(+)
and sno_line_no = sni_line_no(+)
group by sno_ord_no, sno_ord_qty, sno_ord_val
Re: SQL Join [message #276607 is a reply to message #276577] Thu, 25 October 2007 10:45 Go to previous message
milford_f
Messages: 18
Registered: July 2006
Junior Member
Thanks a lot Cthulhu

It works, i am off for the day now, will test it on my live data on sunday.

Thanks once again.

Cheers
Milford.
Previous Topic: Column not allowed here
Next Topic: TRANSLATE and REPLACE
Goto Forum:
  


Current Time: Mon Dec 05 14:53:24 CST 2016

Total time taken to generate the page: 0.11488 seconds