Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #38936] |
Wed, 29 May 2002 01:38 |
M.N.Swaminathan
Messages: 10 Registered: May 2002
|
Junior Member |
|
|
hi there,
I am having two table like the following
Sales ( LocCode,ProdCode,QtySold)
"S01","3GP100ED",10
"S02","3GP100ED",12
"S03","GT0040GS",10
Return( LocCode,ProdCode,QtyRetu)
"S01","3GP100ED",2
"S01","GT0030GS",4
"S03","GT0040GS",3
I WANT A QUERY TO HAVE LIKE FOLLOWING RESULTSET
Target ( LocCode,ProdCode,QtySold,QtyRetu,NetSales)
"S01","3GP100ED",10,2,8
"S01","GT0030GS",0,4,-4
"S02","3GP100ED",12,0,12
"S03","GT0040GS",10,3,7
immediate response will be appreciated
regards
Swami
|
|
|
|
Re: SQL Query [message #38939 is a reply to message #38936] |
Wed, 29 May 2002 09:07 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
sql>select m.loccode, m.prodcode,
2 nvl(s.qtysold, 0) qtysold, nvl(r.qtyretu, 0) qtyretu,
3 nvl(s.qtysold, 0) - nvl(r.qtyretu, 0) netsales
4 from (select loccode, prodcode from sales
5 union
6 select loccode, prodcode from returns) m,
7 sales s, returns r
8 where s.loccode (+)= m.loccode
9 and s.prodcode (+)= m.prodcode
10 and r.loccode (+)= m.loccode
11 and r.prodcode (+)= m.prodcode;
LOC PRODCODE QTYSOLD QTYRETU NETSALES
--- -------- --------- --------- ---------
S01 3GP100ED 10 2 8
S01 GT0030GS 0 4 -4
S02 3GP100ED 12 0 12
S03 GT0040GS 10 2 8
|
|
|
Goto Forum:
Current Time: Tue May 21 08:40:14 CDT 2024
|