SQL & PL/SQL

If you are on 10g, this sounds like it can be solved using the SQL model clause, using "iterate" to calculate the d column.

Regards,
Rob.

I read a bit on models and I don't see how I could use that here.

I also tried using package variables but they need to be reset somewhere and doing so in the before-report trigger results in terrible OCI errors, connections breaking etc etc, and is thus not usable...

> I tried to use lag to do this but it cannot lag over the same column I am calculating at that time, so

lag(D,1) order by (whatever) as D
will not work.

I'm not sure what you mean by "lag over the same column I am calculating at that time". This seems to work fine:

CREATE TABLE testit
( a INTEGER
, b INTEGER
, c INTEGER
, d INTEGER
, whatever VARCHAR2(10) );

INSERT ALL
INTO testit VALUES (1,8, 99,1,'Banana')
INTO testit VALUES (1,13,98,2,'Hatstand')
INTO testit VALUES (2,21,96,3,'Chelsea')
INTO testit VALUES (3,34,96,4,'Paris')
INTO testit VALUES (5,55,95,5,'Bermondsey')
SELECT * FROM dual;

SELECT a, b, c, d AS d_old
     , whatever
     , LAG(d,1) OVER (ORDER BY whatever) AS d
FROM   testit t
ORDER BY whatever;

      A       B       C   D_OLD WHATEVER         D
------- ------- ------- ------- ---------- -------
      1       8      99       1 Banana     
      5      55      95       5 Bermondsey       1
      2      21      96       3 Chelsea          5
      1      13      98       2 Hatstand         3
      3      34      96       4 Paris            2

5 rows selected

Message was edited by:
William Robertson
Changed test values to make results clearer

Cute, but the D in lag(d,1) is de original column d and not the alias you created.

Try this:


SELECT a, b, c, d
, LAG(e,1) OVER (ORDER BY whatever) AS e
FROM testit t;

this is what I want.
if no calculations are added I would expect all values to be the same (i.e. null)
In my query,depending on the value of a, either b or c are added to previous e to create new e, or the value of e becomes the current value of d.

I'm not sure what a recursive analytic function would do if such a thing were possible.

You can apply arbitrary conditions using CASE etc, for example

SELECT a, b, c, d
     , whatever
     , CASE MOD(a,2)
           WHEN 0 THEN -99
           ELSE LAG(d,1) OVER (ORDER BY whatever)
       END AS d
FROM   testit t
ORDER BY whatever;

      A       B       C       D WHATEVER         D
------- ------- ------- ------- ---------- -------
      1       8      99       1 Banana     
      5      55      95       5 Bermondsey       1
      2      21      96       3 Chelsea        -99
      1      13      98       2 Hatstand         3
      3      34      96       4 Paris            2

5 rows selected

Analytics work on the existing result set. They don't work on their own output. Possibly you may be able to implement this by multiple passes represented as in-line views.

A proper example with data might help.

William : you are not getting what I mean. In your query I see two columns named 'D', and in the lag it is using the column ORIGINALLY named D and not the one you gave the alias.
The latter column is the one I want to use.

padders: creating inline views could work, if i knew how many levels I'd have to recurse in. I don't, the list might be 20 entries long, and every row has a value that is based on every row before it.

Let's use William's table for my example. My query would look like this:

select
a,b,c,d,
case when a = 1 then d else
case when a = 2 then lag(e,1) over (order by a) + b else
case when a = 3 then lag(e,1) over (order by a) + c else
null
end
end
end as e
from testit

should give me:
1,8,1,99,99 -- the value of d
1,13,2,98,98 -- the value of d
2,21,3,96,119 -- (98 plus 21)
3,34,4,96,123 -- (119 plus 4)
5,55,5,95,null

instead I get 'invalid identifier' for e.
How should I solve this?

I know what you mean Wendy, and you're not going to achieve it using just lag, and as you now point out the the number of rows being processed is indeterminate then the inline view option is out the window.

Hmm.... tricky one.

I've not done much with the model clause, but maybe this example would help from the manual:

SELECT country, year, sale, csum
FROM
  (SELECT country, year, SUM(sale) sale
   FROM sales_view_ref
   GROUP BY country, year
  )
  MODEL DIMENSION BY (country, year)
        MEASURES (sale, 0 csum)
        RULES (csum[any, any]=
               SUM(sale) OVER (PARTITION BY country
                               ORDER BY year
                               ROWS UNBOUNDED PRECEDING)
              )
  ORDER BY country, year;

COUNTRY         YEAR       SALE       CSUM
--------------- ---------- ---------- ----------
France                1998    4900.25    4900.25
France                1999    5959.14   10859.39
France                2000    4275.03   15134.42
France                2001    5433.63   20568.05
Germany               1998   12943.98   12943.98
Germany               1999   14609.58   27553.56
Germany               2000   10012.77   37566.33
Germany               2001   15991.21   53557.54

8 rows selected.

This is obviously a cumulative sum, but perhaps if we knew what "cumulative" calculation you were trying to achieve on the data then we could figure it out better.

Blushadow, that confirms I will never understand MODEL. It might as well be Fortran.

If we are after a running total, perhaps we should be looking at SUM() and not LAG().

I think this may be easier than it first seems. I added an ID# column to provide the overall order.

Not sure what you expect to happen on that last row (a = 5) though.

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE testit (
  2     id# INT, a INT, b INT, c INT, d INT);

Table created.

SQL> INSERT ALL
  2        INTO testit
  3        VALUES (1, 1, 8, 1, 99)
  4        INTO testit
  5        VALUES (2, 1, 13, 2, 98)
  6        INTO testit
  7        VALUES (3, 2, 21, 3, 96)
  8        INTO testit
  9        VALUES (4, 3, 34, 4, 96)
 10        INTO testit
 11        VALUES (5, 5, 55, 5, 95)
 12     SELECT *
 13     FROM   DUAL;

5 rows created.

SQL> SELECT a, b, c, d,
  2         CASE WHEN a IN (1, 2, 3) THEN
  3            SUM (DECODE (a, 1, d, 2, b, 3, c)) OVER (
  4            PARTITION BY e ORDER BY id#) END n
  5  FROM  (SELECT SUM (DECODE (a, 1, 1)) OVER (
  6                   ORDER BY id#) e, t.*
  7         FROM   testit t);

         A          B          C          D          N
---------- ---------- ---------- ---------- ----------
         1          8          1         99         99
         1         13          2         98         98
         2         21          3         96        119
         3         34          4         96        123
         5         55          5         95

SQL> 

Impressive solution, padders !

With the SQL model clause, the same can be achieved like this:

SQL> CREATE TABLE testit (id# INT, a INT, b INT, c INT, d INT)
  2  /

Tabel is aangemaakt.

SQL> INSERT ALL
  2  INTO testit VALUES (1, 1, 8, 1, 99)
  3  INTO testit VALUES (2, 1, 13, 2, 98)
  4  INTO testit VALUES (3, 2, 21, 3, 96)
  5  INTO testit VALUES (4, 3, 34, 4, 96)
  6  INTO testit VALUES (5, 5, 55, 5, 95)
  7  SELECT * FROM      DUAL
  8  /

5 rijen zijn aangemaakt.

SQL> SELECT a
  2       , b
  3       , c
  4       , d
  5       , CASE
  6         WHEN a IN (1, 2, 3)
  7         THEN
  8           SUM (DECODE (a, 1, d, 2, b, 3, c)) OVER (PARTITION BY e ORDER BY a)
  9         END n
 10  FROM  (SELECT SUM (DECODE (a, 1, 1)) OVER (ORDER BY id#) e, t.*
 11         FROM   testit t
 12        )
 13  /

         A          B          C          D          N
---------- ---------- ---------- ---------- ----------
         1          8          1         99         99
         1         13          2         98         98
         2         21          3         96        119
         3         34          4         96        123
         5         55          5         95

5 rijen zijn geselecteerd.

SQL> select a, b, c, d
  2    from (select * from testit order by id#)
  3   model
  4         dimension by (id#)
  5         measures (a,b,c,d)
  6         rules
  7         ( d[any] = case a[cv()]
  8                    when 1 then d[cv()]
  9                    when 2 then d[cv()-1] + b[cv()]
 10                    when 3 then d[cv()-1] + c[cv()]
 11                    else null
 12                    end
 13         )
 14   order by id#
 15  /

         A          B          C          D
---------- ---------- ---------- ----------
         1          8          1         99
         1         13          2         98
         2         21          3        119
         3         34          4        123
         5         55          5

5 rijen zijn geselecteerd.

Quite readable, isn't it William ?

Regards,
Rob.

Message was edited by:
Rob van Wijk

Added the ordering on id# to prevent ORA-32637

I can't wait to get a 10g database to mess around with so I can bang my head into the wall trying to figure out these model clauses. They look 40% cool 60% aggravating...just enough for me to be entertained. :-)

Unfortunately this solution does not work for me. It seems that when using MODEL you can only select the columns it concerns, and I need a whole lot more information.

The base query is a UNION of 3 queries, which needs to be sorted in a particular way before I can use the model clause. When I try this I get that ORA-32637: Self cyclic rule in sequential order MODEL error.

I guess I am sticking with the package variable, which has been working fine for me so far.

Did you order on your dimension column(s) ?

I don't want to, I want to order by a customer id, a valuta id, and a date

Yes, but you can do that after you are done modelling. But it may be worth the try to order on your dimension column before you enter the model, like I did in my example.