Re: Help with Oracle Analytic functions

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 21 Mar 2003 10:54:43 -0800
Message-ID: <130ba93a.0303211054.33407b25@posting.google.com>

Analytic functions with case should do it:

SQL> select a,b,c,

  2        ( case c
  3               when 0 then null
  4               else (min(b) over (partition by a))
  5               end) min_b, 
  6         sum(b) over (partition by a order by b) tot_b

  7 from test;

         A B C MIN_B TOT_B ---------- ---------- ---------- ---------- ----------

         1          1          1          1          1
         1          3          1          1          4
         2          1          0                     1
         3          6          1          6          6
         3          8          0                    14
         3          9          0                    23
         4         16          0                    16
         4         21          1         16         37
         4         61          1         16         98

9 rows selected.

SQL>

• Jusung Yang

mmontreaux_at_hotmail.com (Monty) wrote in message news:<6284dd3.0303210653.6f64bc6_at_posting.google.com>...
> Hi everyone, I have a requirement that I think will require the use of
> Oracle analytic functions. Unfortunately I'm a bit dumbfounded by
> this. If someone could suggest a solution to my dilemma or point me in
> the right direction I would be most grateful.
> Thank you
> Monty
>
>
> CREATE TABLE test(a NUMBER, b NUMBER, c NUMBER);
> INSERT INTO test(a,b,c) VALUES (1,1,1);
> INSERT INTO test(a,b,c) VALUES (1,3,1);
> INSERT INTO test(a,b,c) VALUES (2,1,0);
> INSERT INTO test(a,b,c) VALUES (3,6,1);
> INSERT INTO test(a,b,c) VALUES (3,8,0);
> INSERT INTO test(a,b,c) VALUES (3,9,0);
> INSERT INTO test(a,b,c) VALUES (4,16,0);
> INSERT INTO test(a,b,c) VALUES (4,21,1);
> INSERT INTO test(a,b,c) VALUES (4,61,1);
>
> SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a ASC, b
> ASC;
>
> What I would like is for SomethingElse1 to be
> a) Whenever c is 0, SomethingElse1 is null.
> b) Whenever c is 1, SomethingElse1 is the minimum value for b
> for all other rows that have the same a value.
>
> What I would like is for SomethingElse2 to be
> c) SomethingElse2 is the commulative total of b's for all the
> rows that have the same a value when ordered a ASC, b ASC as
> above.
>
> ie, the output of the query would be:
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> SELECT a,b,c,SomethingElse1,SomethingElse2 FROM test ORDER BY a
> ASC,b ASC;
>
> A B C SomethingElse1 SomethingElse2
> ---------- ---------- ---------- ------------------ -----------------
> 1 1 1 1 1
> 1 3 1 1 4
> 2 1 0 1
> 3 6 1 6 6
> 3 8 0 14
> 3 9 0 23
> 4 16 0 16
> 4 21 1 16 37
> 4 61 1 16 98
>
> 9 rows selected.
>
> SQL>
>
> We are currently using Oracle 8.1.7 under Windows.
Received on Fri Mar 21 2003 - 12:54:43 CST