Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #38936] Wed, 29 May 2002 01:38 Go to next message
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 #38937 is a reply to message #38936] Wed, 29 May 2002 02:33 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
More something for the SQL message board than for the PL/SQL board, if you ask me.

It is basic mathematics:

SELECT sal, comm, (sal+comm) total_sal
FROM emp;

It is a simple equi-join and some minus, shouldn't be a problem.

MHE
Re: SQL Query [message #38939 is a reply to message #38936] Wed, 29 May 2002 09:07 Go to previous message
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
Previous Topic: The trigger still not work, Does anyone can help please
Next Topic: MS OLE DB Provider for ODBC Drivers error 80040e14 Getting Syntax error with this code: Can you hel
Goto Forum:
  


Current Time: Thu Mar 28 18:04:55 CDT 2024