Home » SQL & PL/SQL » SQL & PL/SQL » SQL Join
SQL Join [message #276501] |
Thu, 25 October 2007 06:21 |
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 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #276533 is a reply to message #276501] |
Thu, 25 October 2007 07:46 |
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 |
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 |
joy_division
Messages: 4963 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 |
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 |
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 |
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 |
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.
|
|
|
Goto Forum:
Current Time: Mon Dec 02 08:50:37 CST 2024
|