Re: sql question

From: metzguar <urs_at_ursmetzger.de>
Date: 25 May 2005 05:04:29 -0700
Message-ID: <1117022669.832713.76100@z14g2000cwz.googlegroups.com>

No error handling, col1, col2 and col3 must all be there and be unique and so on...

SQL> SELECT col1, col2, (s - k / p) AS "wrong formula", (s - k) / p AS "correct formula"
  2 FROM (SELECT col1, col2, col3, LEAD(val) OVER(ORDER BY col1, col3) AS s,

  3                   LAG(val) OVER(ORDER BY col1, col3) AS k, val AS p
  4              FROM t
  5          ORDER BY col1, col3)

  6 WHERE col3 = 'p';

      COL1 COL2 wrong formula correct formula ---------- ---------- ------------- ---------------

         1          2            43      -,92857143
         4          5          94,5      ,681818182

Hth, Urs Received on Wed May 25 2005 - 07:04:29 CDT