Oracle-L: RE: sql query on substract

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 26 Jul 2002 03:08:22 -0800
Message-ID: <F001.004A322C.20020726030822@fatcity.com>

8.1.6 and above:

1 Select datex, s_sum, nvl(s_sum-a,0) from 2 (Select to_char(s_month,'MON') datex,

  3         s_sum,
  4         lag(s_sum,1) over (order by s_month) A,
  5         lead(s_sum,1) over (order by s_month) B

6* from jonny99) x
SQL> / DATEX S_SUM NVL(S_SUM-A,0)
--------- ---------- --------------

JAN             1000              0
FEB             1200            200
MAR             1250             50

I left the lead operator in there so you could take the SQL from the in-line, play with it, and see how it works. Of course, remove s_sum from the main query to get the exact output you desire.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

  -----Original Message-----
  From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonny Delmont   Sent: Friday, July 26, 2002 4:43 AM
  To: Multiple recipients of list ORACLE-L   Subject: sql query on substract

How to substract my s_sum column in sql statement ?

S_MONTH S_SUM

---------- JAN 1000 FEB 1200 MAR 1250

I want the result should look like this :

S_MONTH S_SUM

---------- JAN 0 FEB 200 MAR 50

Any idea please?

Thanks,
Jonny

--

Get a bigger mailbox -- choose a size that fits your needs.

http://uk.docs.yahoo.com/mail_storage.html

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Larry Elkins
INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jul 26 2002 - 06:08:22 CDT