SQL & PL/SQL

Hi, What exactly do you want to do in that case?
Perhaps adding more expressions to the ORDER BY clause will do it.

select    tdt,tcode, tno, tdramt, tcramt, 
	  sum (tdramt - tcramt) over ( order by  tdt
	    	      	      	       ,       	 tcode
				       ,	 tno
				       ,	 tcramt     -- NEW
				     ) ba 
from      tb
order by  tdt
,     	  tcode
,	  tno
,	  tcramt
;

Output:

TDT             TCO        TNO     TDRAMT     TCRAMT         BA
--------------- --- ---------- ---------- ---------- ----------
01-Jan-2010 Fri exn          1        100          0        100
01-Jan-2010 Fri exn          1          0        100          0
02-Jan-2010 Sat exn          2          0        100       -100
03-Jan-2010 Sun exn          3        250          0        150
03-Jan-2010 Sun exn          4          0         50        100
04-Jan-2010 Mon exn          4          0        150        -50

try this...
sum(tdramt-tcramt) over (order by tdt,tcode,tno,tcramt,tdramt) ba

***un-tested***

Hi Frank,

Yes, that does it. But the point is that the set of columns in the order by clause should be unique to make this work. In this case, adding tcramt works, but if both tcramt's would be the same (say 100 both) then this still does not work. I'd add rownum instead.

Regards,
Rob.

Why not to simply use windowing as intended:

with tb as (
select to_date('01/01/2010','dd/mm/yyyy') tdt, 'exn' tcode,1 tno,100 tdramt,0 tcramt from dual union all
select to_date('01/01/2010','dd/mm/yyyy'), 'exn',1, 0,100 from dual union all
select to_date('02/01/2010','dd/mm/yyyy'), 'exn',2, 0,100 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',3, 250,0 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',4, 0,50 from dual union all
select to_date('04/01/2010','dd/mm/yyyy'), 'exn',4, 0,150 from dual
)
select tdt,tcode, tno, tdramt, tcramt,
sum(tdramt-tcramt) over (order by tdt,tcode,tno rows between unbounded preceding and current row) ba from tb
/

TDT       TCO        TNO     TDRAMT     TCRAMT         BA
--------- --- ---------- ---------- ---------- ----------
01-JAN-10 exn          1        100          0        100
01-JAN-10 exn          1          0        100          0
02-JAN-10 exn          2          0        100       -100
03-JAN-10 exn          3        250          0        150
03-JAN-10 exn          4          0         50        100
04-JAN-10 exn          4          0        150        -50

6 rows selected.

SQL> 

SY.

Hi Solomon,

Cool!
I was totally confused by your answer, because it looked like you added the default window clause, but it's not: you changed it from "range between unbounded preceding and current row" to "rows between unbounded preceding and current row". This means a logical offset which is deterministic.

The documentation states:
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.

I like your windowing clause better than using rownum.

Regards,
Rob.

Edited by: Rob van Wijk on 21-mei-2010 22:45

Cleared confusion

Hi Rob,

You missed most important piece of documentation:
And range means ALL rows with same CURRENT ROW value.

SY.

Thanks, I edited the post during your post, when I saw the cause of my confusion.
Learned something new today!

Hehe there is a little simpler :D

with tb as (
select to_date('01/01/2010','dd/mm/yyyy') tdt, 'exn' tcode,1 tno,100 tdramt,0 tcramt from dual union all
select to_date('01/01/2010','dd/mm/yyyy'), 'exn',1, 0,100 from dual union all
select to_date('02/01/2010','dd/mm/yyyy'), 'exn',2, 0,100 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',3, 250,0 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',4, 0,50 from dual union all
select to_date('04/01/2010','dd/mm/yyyy'), 'exn',4, 0,150 from dual)
select tdt,tcode, tno, tdramt, tcramt,
sum(tdramt-tcramt) over
(order by tdt,tcode,tno rows unbounded preceding) ba
from tb;

TDT       TCO  TNO  TDRAMT  TCRAMT    BA
--------  ---  ---  ------  ------  ----
10-01-01  exn    1     100       0   100
10-01-01  exn    1       0     100     0
10-01-02  exn    2       0     100  -100
10-01-03  exn    3     250       0   150
10-01-03  exn    4       0      50   100
10-01-04  exn    4       0     150   -50

Thank you guys for sharing your valuable information..

i have a bit different requirement now, since i put the above code to use in my report.

i want to always show on the first row the 'Opening Balance' record. For that,
i am inserting a row, that takes it data from a subquery before the start date from the period specified
and UNIONing it with the data from the other subquery that falls between the start and end date of the period specified.

i modified the data and its query to this , but couldnt get the Opening Balance row on top (as my first row).

by Solomon Y method;-

SQL> with tb as (
  2  select to_date('01/01/2010','dd/mm/yyyy') tdt, 'exn' tcode,1 tno,'Transaction 1' Narration, 100 tdramt,0 tcramt from dual union all
  3  select to_date('01/01/2010','dd/mm/yyyy'), 'exn',1,'Transaction 2',  0,100 from dual union all
  4  select to_date('02/01/2010','dd/mm/yyyy'), 'exn',2,'Transaction 3',  0,100 from dual union all
  5  select to_date('03/01/2010','dd/mm/yyyy'), 'axn',3,'Transaction 4',  250,0 from dual union all
  6  select to_date('03/01/2010','dd/mm/yyyy'), 'exn',4,'Transaction 5',  0,50 from dual union all
  7  select to_date('04/01/2010','dd/mm/yyyy'), 'axn',4,'Transaction 6',  0,150 from dual
  8  )
  9  select tdt, tcode, tno, Narration,tdramt, tcramt,
 10  sum(tdramt-tcramt) over (order by tdt,tcode,tno rows between unbounded preceding and current row) ba from
 11  (
 12  select to_date('01/01/2010','dd/mm/yyyy') tdt, null tcode,null tno, 'Opening Balance' Narration, 100 tdramt,100 tcramt from dual 
 13  union all
 14  select tdt,tcode, tno, narration, tdramt, tcramt from tb)
 15  order by tdt,tcode,tno;

TDT       TCO           TNO NARRATION              TDRAMT        TCRAMT            BA
--------- --- ------------- --------------- ------------- ------------- -------------
01-JAN-10 exn         1.000 Transaction 1         100.000          .000       100.000
01-JAN-10 exn         1.000 Transaction 2            .000       100.000          .000
01-JAN-10                   Opening Balance       100.000       100.000          .000      <== This row must always be the first row of the query result
02-JAN-10 exn         2.000 Transaction 3            .000       100.000      -100.000
03-JAN-10 exn         3.000 Transaction 4         250.000          .000       150.000
03-JAN-10 exn         4.000 Transaction 5            .000        50.000       100.000
04-JAN-10 exn         4.000 Transaction 6            .000       150.000       -50.000

7 rows selected.

Even with Frank Kulash method, the desired result is not achieved.

Kindly suggest , how to achieve this now. i am using 10g

TY.

A quick solution could be

select tdt, 
       tcode, 
       tno, 
       Narration,
       tdramt, 
       tcramt,
       sum(tdramt-tcramt) over (order by the_order,tdt,tcode,tno rows between unbounded preceding and current row) ba 
  from (select 0 the_order,
               to_date('01/01/2010','dd/mm/yyyy') tdt, 
               null tcode,
               null tno, 
               'Opening Balance' Narration, 
               100 tdramt,
               100 tcramt 
          from dual 
        union all
        select 1,
               tdt,
               tcode, 
               tno, 
               narration, 
               tdramt, 
               tcramt 
          from tb
       )
 order by the_order,tdt,tcode,tno;

Regards

Etbin

Edited by: Etbin on 23.5.2010 12:35
the opening ballance row must participate in calculations

Hi,

Are you saying that the query you posted is producing the right data; the only problem is the order of those rows?

It looks like the data falls into two groups, corresponding to the two branches of the UNION:
(1) the "Opening Balance" row
(2) everything else
The most robust way to get that order is to add a new column (called grp in the query below) that has the value 1 or 2, corresponding to the items above. This will be the first item in the ORDER BY clause. After that comes what you're already ORDERing BY. You already know this will produce the correct order within group (2). Group (1) will only have one row, so anything after grp in the ORDER BY clause won't affect group (1)

SELECT    tdt, tcode, tno, Narration,tdramt, tcramt,
  	  SUM (tdramt - tcramt) OVER ( ORDER BY  tdt
	    	      	      	       ,	 tcode
				       ,	 tno 
				       ROWS BETWEEN    UNBOUNDED PRECEDING 
				     	    AND        CURRENT ROW
			             ) AS ba 
FROM      (
  	      SELECT  TO_DATE ('01/01/2010','dd/mm/yyyy')	AS tdt
	      ,	      NULL    					AS tcode
	      ,	      NULL					AS tno
	      ,	      'Opening Balance' 			AS Narration
	      ,	      100      					AS tdramt
	      ,	      100					AS tcramt
	      ,	      1						AS grp
	      FROM    dual 
	      	      --
		  UNION ALL
		      --
	     SELECT  tdt
	     ,	     tcode
	     ,	     tno
	     ,	     narration
	     ,	     tdramt
	     ,	     tcramt
	     ,	     2						AS grp 
	     FROM    tb
	  )
ORDER BY  grp
,     	  tdt
,     	  tcode
,	  tno;

Edited by: Frank Kulash on May 24, 2010 1:42 PM
Which is exactly what Etbin suggested. (I didn't see his message at first.)

Sorry for jumping so late in this thread, but maybe, you could ellaborate a little, why do you think, this is intended way to use windowing?
In particular - i don't understand, why replacing logical offsets by physical offsets without unique sort key can be considered as proper way to work with windows (equally well i don't understand Rob's suggestion, how adding rownum could produce deterministic result in the form asked by op)


Best regards

Maxim