Re: Retrive values from different rows

From: <rwijk72_at_gmail.com>
Date: Tue, 19 Jun 2007 05:30:39 -0700
Message-ID: <1182256239.044170.103650@n60g2000hse.googlegroups.com>

> Perfect! I've already put in production your tips and it works great.
> It's *exactly* what I was looking for!
> Thank you very much.

It gives the results you want, but it is scanning the table twice, so it is needlessly slow.
If you use the analytic function SUM it is more performant.

See:

SQL> create table thetable
2 as
3 select 1 id, 'A' art, 10 qta, 'W' reason from dual unio

  4  select 2, 'A', -2, 'C' from dual union all
  5  select 3, 'A', 3, 'PO' from dual union all
  6  select 4, 'A', -4, 'C' from dual union all
  7  select 5, 'B', 5, 'W' from dual union all
  8  select 6, 'B', -2, 'C' from dual union all
  9  select 7, 'B', -4, 'C' from dual union all
 10  select 8, 'B', 5, 'PO' from dual union all
 11  select 9, 'C', 5, 'W' from dual union all

12 select 10, 'D', 7, 'W' from dual
13 /

Tabel is aangemaakt.

SQL> set autotrace on explain
SQL> SELECT ID, art, qta, reason

  2         , CASE
  3              WHEN reason = 'W'
  4                 THEN qta
  5              ELSE (SELECT SUM (qta)
  6                      FROM thetable
  7                     WHERE art = t.art AND ID <= t.ID)
  8           END AS val
  9      FROM thetable t

10 ORDER BY ID
11 /

ID A QTA RE VAL
----- - ----- -- -----

    1 A    10 W     10
    2 A    -2 C      8
    3 A     3 PO    11
    4 A    -4 C      7
    5 B     5 W      5
    6 B    -2 C      3
    7 B    -4 C     -1
    8 B     5 PO     4
    9 C     5 W      5
   10 D     7 W      7

10 rijen zijn geselecteerd.

Uitvoeringspan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)
   2 1 TABLE ACCESS (FULL) OF 'THETABLE'    3 0 SORT (ORDER BY)
   4 3 TABLE ACCESS (FULL) OF 'THETABLE' SQL> select id

  2       , art
  3       , qta
  4       , reason
  5       , sum(qta) over (partition by art order by id) val

  6 from thetable
  7 order by id
  8 /

ID A QTA RE VAL
----- - ----- -- -----

    1 A    10 W     10
    2 A    -2 C      8
    3 A     3 PO    11
    4 A    -4 C      7
    5 B     5 W      5
    6 B    -2 C      3
    7 B    -4 C     -1
    8 B     5 PO     4
    9 C     5 W      5
   10 D     7 W      7

10 rijen zijn geselecteerd.

Uitvoeringspan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY)

   2    1     WINDOW (SORT)
   3    2       TABLE ACCESS (FULL) OF 'THETABLE'

Regards,
Rob. Received on Tue Jun 19 2007 - 07:30:39 CDT