## Forum Stats

• 3,734,039 Users
• 2,246,863 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Intersperse daily totals with opening and closing balance

Member Posts: 217
edited April 2010
Hi All

Suppose we have a data set (simplified from my query but will suffice)
```(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)```
How would you go about getting a result set that looks like:

1, Opening Balance, 0
1, CR, 200
1, DR, -300
1, Closing Balance, -100
2, Opening Balance, -100
2, CR, 800
2, DR, -200
2, Closing Balance, 500
3, Opening Balance, 500
3, CR, 900
3, DR, -500
3, Closing Balance, 900
4, Opening Balance, 900
4, Closing Balance, 900

I'm thinking:
generate an arbitrary number of rows for the number of days I want to report over (in this case, 4)
cross join these with 2 row set of constants "opening" and "closing" balanace
-> I have now an opening and closing balance row for every day
union it with my data so that "opening/CLosing balance" is a type too (and nulls in the amount column)
case when the type is like '%Balance' then sum(amount) over(order by day, orderkind rows unbounded preceding) else amount

(orderkind is a constant 0 for opening, 1 for all the datarows, 2 for closing)

Is it right?
Is there a better way?

Edited by: charred on Apr 1, 2010 7:03 AM

• Member Posts: 16,076 Gold Crown
Your question bugged me for quite a while, especially since it looks so easy, but today I got a chance to ask and got an answer:

• Member Posts: 5,856 Bronze Trophy
Or using the model clause:
```SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,cast(trantype as varchar2(15)) trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype <> 'Closing balance']
18         , amount[daynum <> 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /

DAYNUM TRANTYPE            AMOUNT
---------- --------------- ----------
1 Opening balance          0
1 CR                     200
1 DR                    -300
1 Closing balance       -100
2 Opening balance       -100
2 CR                     800
2 DR                    -200
2 Closing balance        500
3 Opening balance        500
3 CR                     900
3 DR                    -500
3 Closing balance        900
4 Opening balance        900
4 Closing balance        900

14 rows selected.```
Regards,
Rob.
• Member Posts: 16,076 Gold Crown
Hi Rob,

Thanks a lot, very neat example!
Unfortunatly it refuses to run on 10.2
What point am I missing?
```SQL> select banner from v\$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,cast(trantype as varchar2(15)) trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype <> 'Closing balance']
18         , amount[daynum  1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /
, amount[daynum  1,'Opening balance'] = amount[cv()-1,'Closing balance']
*
ERROR at line 18:
ORA-00905: missing keyword```
• Member Posts: 6,871
edited April 2010
hoek --

Is it the forum removing the &lt;&gt; (not equals) symbol?
• Member Posts: 4,983 Gold Trophy
If you reply to rob's post and quote his post, you'll see that there are &lt;&gt;'s that the forum can't handle still (*sigh*) that are missing from your query.
• Member Posts: 5,856 Bronze Trophy
Hi Martijn,

I've been away too long, so I had forgotten that this forum software makes the unequal sign disappear.
Below is the same query with the other unequal sign (!=) :
```SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,cast(trantype as varchar2(15)) trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /

DAYNUM TRANTYPE            AMOUNT
---------- --------------- ----------
1 Opening balance          0
1 CR                     200
1 DR                    -300
1 Closing balance       -100
2 Opening balance       -100
2 CR                     800
2 DR                    -200
2 Closing balance        500
3 Opening balance        500
3 CR                     900
3 DR                    -500
3 Closing balance        900
4 Opening balance        900
4 Closing balance        900

14 rows selected.```
Regards,
Rob.
• Member Posts: 16,076 Gold Crown
edited April 2010
Aha, ofcourse, should have thought of that.
It's a sign I need either weekend or coffee (actually: both ) ....
Thanks Centinul, Boneist and Rob!

However, Now I run into:
```SQL> with t as
2    (
3      select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4      select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5      select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6      select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7      select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8      select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9    )
10    select *
11      from t
12     model
13           dimension by (daynum, cast(trantype as varchar2(15)) trantype )
14           measures (amount)
15           rules upsert all
16           ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17           , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18           , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19           )
20     order by daynum
21         , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /
from t
*
ERROR at line 11:
ORA-25137: Data value out of range```
It has to do with the cast as varchar2, since:
```SQL> with t as
2    (
3      select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4      select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5      select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6      select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7      select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8      select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9    )
10    select *
11      from t
12     model
13           dimension by (daynum, cast(trantype as char(15)) trantype )
14           measures (amount)
15           rules upsert all
16           ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17           , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18           , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19           )
20     order by daynum
21         , decode(trim(trantype),'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /

DAYNUM TRANTYPE            AMOUNT
---------- --------------- ----------
1 Opening balance          0
1 CR                     200
1 DR                    -300
1 Closing balance       -100
2 Opening balance       -100
2 CR                     800
2 DR                    -200
2 Closing balance        500
3 Opening balance        500
3 CR                     900
3 DR                    -500
3 Closing balance        900
4 Opening balance        900
4 Closing balance        900

14 rows selected.```
char(15) works fine...¿Now that's funky, isn't it?
It's like the opposite of this bug# I found: 8298796: cast to "char" fails whereas cast to "varchar2" succeeds

Rob, my bet is that you're not on DB-version 10.2.0.4, are you?

(By the way, @ charred: I didn't mean to 'hijack' your thread, thought it was worth mentioning.)

Edited by: hoek on Apr 16, 2010 5:10 PM second example: trantype needs to be trimmed in the order by in order to sort as desired.
• Member Posts: 5,856 Bronze Trophy
Hi Martijn,

I used 10.2.0.3 to produce that output.

10.2.0.4 is known to be buggy with the model clause. I wrote about it here: http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
As you can see in that post, I used an extra measure to circumvent that bug, but now I see you can circumvent it as well by using the char datatype.

I'll check it out this evening and report back.

Regards,
Rob.
• Member Posts: 16,076 Gold Crown
Thanks, Rob!
• Member Posts: 4,983 Gold Trophy
edited April 2010
Unless I'm missing something, Tom's answer won't work for more than 4 days?
```with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 4 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'DR' as trantype, -500 as amount from dual
),
data
as
( select daynum, r
from (select level daynum from dual connect by level <= 4),
(select level r from dual connect by level <= 2 )
)
select daynum,
r,
ttype,
amt,
case when ttype in ( 'close', 'open' )
then sum(samt) over (order by daynum, r)
end theamount
from (
select data.daynum,
data.r,
case when (nvl(t.trantype,'CR') = 'CR' and data.r = 1 )
then 'open'
when (nvl(t.trantype,'DR') = 'DR' and data.r = 2)
then 'close'
else t.trantype
end ttype,
case when (t.trantype = 'CR' and r = 2) or (t.trantype = 'DR' and r=1)
then t.amount
end amt,
case when (t.trantype = 'DR' and r = 2)
then sum(case when r=1 then t.amount end) over (partition by data.daynum)
else 0
end samt
from   t, data
where data.daynum (+) = t.daynum
)
order by daynum, r, decode( ttype, 'open', 1, 'CR', 2, 'DR', 3, 'close', 4 );

DAYNUM          R TTYPE        AMT  THEAMOUNT
---------- ---------- ----- ---------- ----------
1          1 open                      0
1          1 DR          -300
1          2 CR           200
1          2 close                  -100
2          1 open                   -100
2          1 DR          -200
2          2 CR           800
2          2 close                   500
3          1 open                    500
3          1 DR          -500
3          2 CR           900
3          2 close                   900
4          1 open                    900
4          1 DR          -500
4          2 CR           900
4          2 close                  1300
CR
DR  ```
Here's a solution using partitioned outer join:
```with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 4 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'DR' as trantype, -500 as amount from dual),
dummy as (select 1 id, 'Opening Balance' descr from dual union all
select 2 id, 'CR' descr from dual union all
select 3 id, 'DR' descr from dual union all
select 4 id, 'Closing Balance' descr from dual)
select mt.daynum,
d.descr,
decode(d.id, 1, 0,
2, amount,
3, amount,
4, amount + lag(amount, 2) over (partition by mt.daynum order by d.id)) amount
from   my_tab mt
left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
where  (d.id = 1 and mt.trantype = 'CR')
or     (d.id = 2 and mt.trantype = 'CR')
or     (d.id = 3 and mt.trantype = 'DR')
or     (d.id = 4 and mt.trantype = 'DR')
order by mt.daynum, d.id;

DAYNUM DESCR               AMOUNT
---------- --------------- ----------
1 Opening Balance          0
1 CR                     200
1 DR                    -300
1 Closing Balance       -100
2 Opening Balance          0
2 CR                     800
2 DR                    -200
2 Closing Balance        600
3 Opening Balance          0
3 CR                     900
3 DR                    -500
3 Closing Balance        400
4 Opening Balance          0
4 CR                     900
4 DR                    -500
4 Closing Balance        400
5 Opening Balance          0
5 CR                     900
5 DR                    -500
5 Closing Balance        400```
ETA: And, if there can be any number of CR or DR rows:
```with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 6 as daynum, 'DR' as trantype, -200 as amount from dual),
dummy as (select 1 id, 'Opening Balance' descr from dual union all
select 2 id, 'CR' descr from dual union all
select 3 id, 'DR' descr from dual union all
select 4 id, 'Closing Balance' descr from dual)
select mt.daynum,
d.descr,
mt.trantype,
decode(d.id, 1, 0,
2, amount,
3, amount,
4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (partition by mt.daynum)) amount
from   (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn
from   my_tab mt1) mt
left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
where  case when d.id = 1 and rn = 1 then 1
when d.id = 2 and mt.trantype = 'CR' then 1
when d.id = 3 and mt.trantype = 'DR' then 1
when d.id = 4 and rn = 1 then 1
else 0
end = 1
order by mt.daynum, d.id;

DAYNUM DESCR           TRANTYPE     AMOUNT
---------- --------------- -------- ----------
1 Opening Balance CR                0
1 CR              CR              400
1 CR              CR              200
1 DR              DR             -300
1 DR              DR             -500
1 Closing Balance CR             -200
2 Opening Balance CR                0
2 CR              CR              800
2 DR              DR             -200
2 Closing Balance CR              600
3 Opening Balance CR                0
3 CR              CR              900
3 DR              DR             -500
3 Closing Balance CR              400
4 Opening Balance CR                0
4 CR              CR              900
4 Closing Balance CR              900
5 Opening Balance CR                0
5 CR              CR              900
5 CR              CR              200
5 DR              DR             -500
5 Closing Balance CR              600
6 Opening Balance DR                0
6 DR              DR             -200
6 Closing Balance DR             -200```
Edited by: Boneist on 16-Apr-2010 17:10
• Member Posts: 5,856 Bronze Trophy
Hi Boneist,
Boneist wrote:
Unless I'm missing something, Tom's answer won't work for more than 4 days?
Then you should adjust the factored subquery "data" like this:
data
as
( select daynum, r
from (select level daynum from dual connect by level <= 5),
(select level r from dual connect by level <= 2 )
)
And then Tom's answer works again.

Here's a solution using partitioned outer join:
Those solutions are resetting every opening balance to 0...

Regards,
Rob.
• Member Posts: 16,076 Gold Crown
Unless I'm missing something
Hi Boneist,

Nice to see how this thread has developed by the way, ( added it to 'my favourite threads' section ), teached me some great stuff I didn't know or totally forgot about.
Tom's example taught me were I went wrong (I got pretty close, but the 'data'-thing would never have crossed my mind)
Rob's example points out that practically anything can be solved using the MODEL clause (at least, that's what I'm beginning to think) and so I really need to get more familiar with it.
And now I see you added another sophisticated beauty, thanks!
• Member Posts: 16,076 Gold Crown
Those solutions are resetting every opening balance to 0...
Spot on, I missed that.
must be the 'vrijmibo' I just returned from
• Member Posts: 5,856 Bronze Trophy
Hi Martijn,

I checked with a number of variants to get a clue about when exactly that 10.2.0.4 bug arises. I will paste all tries here, so you can draw your own conclusions.
```SQL> select * from v\$version
2  /

BANNER
---------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE	10.2.0.4.0	Production
TNS for MacOS X Server: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,cast(trantype as varchar2(15)) trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /
with t as
*
ERROR at line 1:
ORA-25137: Data value out of range

SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from (select daynum,cast(trantype as varchar2(15)) trantype,amount from t)
12   model
13         dimension by (daynum,trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /
with t as
*
ERROR at line 1:
ORA-25137: Data value out of range

SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  , t2 as
11  ( select daynum
12         , cast(trantype as varchar2(15)) trantype
13         , amount
14      from t
15  )
16  select *
17    from t2
18   model
19         dimension by (daynum,trantype)
20         measures (amount)
21         rules upsert all
22         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
23         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
24         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
25         )
26   order by daynum
27       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
28  /
with t as
*
ERROR at line 1:
ORA-25137: Data value out of range

SQL> with t as
2  (
3    select 1 as daynum, cast('CR' as varchar2(15)) trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /

DAYNUM TRANTYPE                                          AMOUNT
---------- --------------------------------------------- ----------
1 Opening balance                                        0
1 CR                                                   200
1 DR                                                  -300
1 Closing balance                                     -100
2 Opening balance                                     -100
2 CR                                                   800
2 DR                                                  -200
2 Closing balance                                      500
3 Opening balance                                      500
3 CR                                                   900
3 DR                                                  -500
3 Closing balance                                      900
4 Opening balance                                      900
4 Closing balance                                      900

14 rows selected.

SQL> with t as
2  (
3    select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
4    select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
5    select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
6    select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
7    select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
8    select 3 as daynum, 'DR' as trantype, -500 as amount from dual
9  )
10  select *
11    from t
12   model
13         dimension by (daynum,cast(trantype as char(15)) trantype)
14         measures (amount)
15         rules upsert all
16         ( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
17         , amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance']
18         , amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
19         )
20   order by daynum
21       , decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
22  /

DAYNUM TRANTYPE                                          AMOUNT
---------- --------------------------------------------- ----------
1 Opening balance                                        0
1 Closing balance                                     -100
1 DR                                                  -300
1 CR                                                   200
2 Opening balance                                     -100
2 Closing balance                                      500
2 DR                                                  -200
2 CR                                                   800
3 Opening balance                                      500
3 Closing balance                                      900
3 DR                                                  -500
3 CR                                                   900
4 Opening balance                                      900
4 Closing balance                                      900

14 rows selected.```
I also checked the string aggregation example from my blogpost. When I change the measure enames to char(40) instead of varchar2(40), the bug doesn't disappear. But you showed that when the bug arises from a casted column name in the dimension, changing the datatype from varchar2 to char, solves the bug. So I still haven't found a good solution, only workarounds:

* When you encounter a "ORA-25137: Data value out of range" in your model clause query in 10.2.0.4, because of a cast to varchar2 function in your dimension clause: switch to using the char datatype
* When you encounter a "ORA-25137: Data value out of range" in your model clause query in 10.2.0.4, because of a cast to varchar2 function in your measure clause: introduce an extra measure for the original column

Regards,
Rob.

PS: it took a few seconds before I understood "vrijmibo" ...
• Member Posts: 16,076 Gold Crown
Hi Rob,
I will paste all tries here, so you can draw your own conclusions.
conclusion #1:
You've reminded me in the first place that the MODEL clause is very very powerful and I will put more time into reading and understanding how it works. (Salim Chelabi pops into my mind, he also posted some great MODEL-related stuff )

At this moment only XE (10.2.0.1) is at my disposal and your example runs without any problem from that.
conclusion #2:
An example that (I think) will not be a widespread applied approach (with clause + model clause + cast) exposed a bug on a certain database version, that you've already encountered and mentioned many moons ago. There are several workarounds possible (by the way: when casting to char, the order by (if needed) needs a trim). So, very few people will run into this...but it's a bug, yes, and I think it's worth filing it anyway. Couldn't find it on metalink/mos, only an 'opposite bug'.
Don't know whether it's already fixed (again) in 11.1/11.2 (those database versions are currently not available to me unfortunatly).

Thanks and regards,

Martijn
PS: it took a few seconds before I understood "vrijmibo" ...
Never mind that, and just to be clear, that expression has nothing to do with a 'popular' site who copied it, it's an 'ancient acronym'-ism.
Just like the 'madiwodo-soup' that's available during lunch on friday (do not eat it)
• Member Posts: 5,856 Bronze Trophy
Hi Martijn,

Yes the model clause is really powerful. And it makes queries relatively easy to solve, when you've taken the first big hurdle to understand how the model clause works. This query is a nice showcase. Three simple rules to calculate what you want.
hoek wrote:
... and I will put more time into reading and understanding how it works. (Salim Chelabi pops into my mind, he also posted some great MODEL-related stuff )
There are three tutorials on my blog about the model clause, you might want to check out when you start learning...
I didn't file the bug since I haven't had trouble with it on a production system.
Don't know whether it's already fixed (again) in 11.1/11.2 (those database versions are currently not available to me unfortunatly).
The bug is not there on 11.1.0.6 and 11.1.0.7. And since 11.2.0.1 has the listagg function, I haven't bothered checking that version. (why don't you install the newest database versions?)
Just like the 'madiwodo-soup' that's available during lunch on friday (do not eat it)
Lol. Reminds me of university days. They called their soup on friday "soup of the week", which was a madiwodo-soup like you describe :-)

Regards,
Rob.
• Member Posts: 18,393 Black Diamond
edited April 2010
>

Just for fun. To make it independent of last daynum:
```with t as (
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select  daynum,
trim(trantype),
amount
from  t
model
dimension by (
daynum,
cast(trantype as char(15)) trantype
)
measures (
amount,
daynum + 1 dnum
)
rules upsert all (
dnum[1,'Opening balance'] = max(dnum)[any,any],
amount[for daynum from 1 to dnum[1,'Opening balance'] increment 1,'Opening balance'] = 0,
amount[any,'Closing balance'] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance'],
amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum,
decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

DAYNUM TRIM(TRANTYPE)      AMOUNT
---------- --------------- ----------
1 Opening balance          0
1 Closing balance       -100
1 DR                    -300
1 CR                     200
2 Opening balance       -100
2 Closing balance        500
2 DR                    -200
2 CR                     800
3 Opening balance        500
3 Closing balance        900
3 DR                    -500

DAYNUM TRIM(TRANTYPE)      AMOUNT
---------- --------------- ----------
3 CR                     900
4 Opening balance        900
4 Closing balance        900

14 rows selected.

SQL> ```
It still assumes daynum starts with 1, but it is not too difficult to make code completely daynum independent.

SY.
• Member Posts: 16,076 Gold Crown
Thanks Rob!

There are three tutorials on my blog about the model clause
I already knew about your great MV-blogposts, and yes, and there's much much more on your blog, just discovering and browsing, this will keep me quiet for a while ;-)
I didn't file the bug since I haven't had trouble with it on a production system.
Yes, I think it's kind of an 'exotic situation' too, cannot imagine this particular case will indicate a 'severe loss of service'.
But it was very nice and learnful to have this all figured out anyway.
why don't you install the newest database versions?
I'd love to, but I can't for the moment, and I will do that right away when I get my sparkling brand new pc, I'm simply not able to install 11.2 due to...uhm...+Oh well, I confess...+ the current available memory on my current ancient pc at home ( please don't tell anyone about this )

Thanks and regards,
Martijn
• Member Posts: 105
edited April 2010
Hi Rob van Wijk

when I run your query just adding two more lines in example data i found this error.
How can i minimize this ?

with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
--------------------------------------------------------
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select *
from t
model
dimension by (daynum,cast(trantype as /*VARCHAR2(15)*/chAR(15)) trantype)
measures (amount)
rules upsert all
( amount[for daynum from 1 to 4 increment 1,'Opening balance'] = 0
, amount[any,'Closing balance'] order by daynum = sum(amount)
[daynum <= cv(daynum),trantype != 'Closing balance']
, amount[daynum != 1,'Opening balance'] = amount[cv()-1,'Closing balance']
)
order by daynum
, decode(trantype,'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

SQL> /
from t
*
ERROR at line 15:
ORA-32638: Non unique addressing in MODEL dimensions

SQL> ED
Wrote file afiedt.buf

Thanks

Halim

Edited by: Abdul Halim on Apr 17, 2010 11:52 PM
• Member Posts: 18,393 Black Diamond
edited April 2010
Abdul Halim wrote:

when I run your query just adding two more lines in example data i found this error.
Rob's solution assumes there is only one transaction per transtype per day. If there can be multiple transactions of same type per day use something like:
```with t as (
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
)
select  daynum,
trim(trantype) trantype,
amount
from  t
model
dimension by(
daynum,
cast(trantype as char(15)) trantype,
row_number() over(partition by daynum,trantype order by 1) rn
)
measures(
amount
)
rules upsert all(
amount[for daynum from 1 to 4 increment 1,'Opening balance',1] = 0,
amount[any,'Closing balance',1] order by daynum = sum(amount)[daynum <= cv(daynum),trantype != 'Closing balance',any],
amount[daynum != 1,'Opening balance',1] = amount[cv()-1,'Closing balance',cv()]
)
order by daynum,
decode(trim(trantype),'Opening balance',1,'CR',2,'DR',3,'Closing balance',4)
/

DAYNUM TRANTYPE            AMOUNT
---------- --------------- ----------
1 Opening balance          0
1 CR                     200
1 CR                     200
1 DR                    -300
1 DR                    -300
1 Closing balance       -200
2 Opening balance       -200
2 CR                     800
2 DR                    -200
2 Closing balance        400
3 Opening balance        400

DAYNUM TRANTYPE            AMOUNT
---------- --------------- ----------
3 CR                     900
3 DR                    -500
3 Closing balance        800
4 Opening balance        800
4 Closing balance        800

16 rows selected.

SQL> ```
SY.
• Member Posts: 1,072 Bronze Badge
I like model clause B-)
```with t as(
select 1 as daynum,'CR' as trantype, 200 as amount from dual union
select 1 as daynum,'DR' as trantype,-300 as amount from dual union
select 2 as daynum,'CR' as trantype, 800 as amount from dual union
select 2 as daynum,'DR' as trantype,-200 as amount from dual union
select 3 as daynum,'CR' as trantype, 900 as amount from dual union
select 3 as daynum,'DR' as trantype,-500 as amount from dual)
select daynum,trantype,nvl(amount,0) as amount
from t
model
dimension by(daynum,cast(trantype as varchar2(20)) as trantype)
measures(amount)
rules(
amount[for daynum in(1,2,3,4),'Opening Balance']
= sum(amount)[daynum< cv(),trantype in('CR','DR')],
amount[for daynum in(1,2,3,4),'Closing Balance']
= sum(amount)[daynum<=cv(),trantype in('CR','DR')])
order by daynum,
decode(trantype,'Opening Balance',1,'CR',2,'DR',3,'Closing Balance',4);

daynum  trantype         amount
------  ---------------  ------
1  Opening Balance       0
1  CR                  200
1  DR                 -300
1  Closing Balance    -100
2  Opening Balance    -100
2  CR                  800
2  DR                 -200
2  Closing Balance     500
3  Opening Balance     500
3  CR                  900
3  DR                 -500
3  Closing Balance     900
4  Opening Balance     900
4  Closing Balance     900```
• Member Posts: 105
Thanks

Regards
Halim
• Member Posts: 4,983 Gold Trophy
Rob van Wijk wrote:
Those solutions are resetting every opening balance to 0...

In which case, this ought to do it:
```with my_tab as (select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 1 as daynum, 'CR' as trantype, 400 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 4 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 5 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 5 as daynum, 'DR' as trantype, -500 as amount from dual union all
select 6 as daynum, 'DR' as trantype, -200 as amount from dual),
dummy as (select 1 id, 'Opening Balance' descr from dual union all
select 2 id, 'CR' descr from dual union all
select 3 id, 'DR' descr from dual union all
select 4 id, 'Closing Balance' descr from dual)
select mt.daynum,
d.descr,
mt.trantype,
decode(d.id, 1, sum(decode(d.id, 1, 0, 4, 0, amount)) over (order by mt.daynum, d.id),
2, amount,
3, amount,
4, sum(decode(d.id, 1, 0, 4, 0, amount)) over (order by mt.daynum, d.id)) amount
from   (select mt1.*, row_number() over (partition by mt1.daynum order by mt1.trantype) rn
from   my_tab mt1) mt
left outer join dummy d partition by (id, descr) on (mt.trantype = d.descr)
where  case when d.id = 1 and rn = 1 then 1
when d.id = 2 and mt.trantype = 'CR' then 1
when d.id = 3 and mt.trantype = 'DR' then 1
when d.id = 4 and rn = 1 then 1
else 0
end = 1
order by mt.daynum, d.id;

DAYNUM DESCR           TRANTYPE     AMOUNT
---------- --------------- -------- ----------
1 Opening Balance CR                0
1 CR              CR              400
1 CR              CR              200
1 DR              DR             -300
1 DR              DR             -500
1 Closing Balance CR             -200
2 Opening Balance CR             -200
2 CR              CR              800
2 DR              DR             -200
2 Closing Balance CR              400
3 Opening Balance CR              400
3 CR              CR              900
3 DR              DR             -500
3 Closing Balance CR              800
4 Opening Balance CR              800
4 CR              CR              900
4 Closing Balance CR             1700
5 Opening Balance CR             1700
5 CR              CR              900
5 CR              CR              200
5 DR              DR             -500
5 Closing Balance CR             2300
6 Opening Balance DR             2300
6 DR              DR             -200
6 Closing Balance DR             2100```
• Member Posts: 13,711 Bronze Badge
Joining the party very late i guess
```with t as
(
select 1 as daynum, 'CR' as trantype, 200 as amount from dual union all
select 1 as daynum, 'DR' as trantype, -300 as amount from dual union all
select 2 as daynum, 'CR' as trantype, 800 as amount from dual union all
select 2 as daynum, 'DR' as trantype, -200 as amount from dual union all
select 3 as daynum, 'CR' as trantype, 900 as amount from dual union all
select 3 as daynum, 'DR' as trantype, -500 as amount from dual
), bal as
(
select t.daynum, b.trantype, 0 amount, b.seqval
from (
select distinct daynum from t
) t
cross join
(
select 'OPEN Balance' trantype, 1 seqval from dual
union all
select 'CLOSE Balance' trantype, 3 seqval from dual
) b
)
select daynum, trantype, case when seqval not in (1,3) then amount else sum(amount) over(order by daynum, seqval) end amount
from (
select daynum, trantype, amount, 2 seqval
from t
union all
select daynum, trantype, amount, seqval
from bal
)```
• Member Posts: 16,076 Gold Crown
Better late than never!
This discussion has been closed.