Forum Stats

  • 3,734,039 Users
  • 2,246,863 Discussions
  • 7,857,010 Comments

Discussions

Intersperse daily totals with opening and closing balance

445476
445476 Member Posts: 217
edited April 2010 in SQL & PL/SQL
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

Answers

  • Hoek
    Hoek 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:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2559625300346885321
  • Rob van Wijk
    Rob van Wijk 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.
  • Hoek
    Hoek 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
  • Centinul
    Centinul Member Posts: 6,871
    edited April 2010
    hoek --

    Is it the forum removing the &lt;&gt; (not equals) symbol?
  • Boneist
    Boneist 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.
  • Rob van Wijk
    Rob van Wijk 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.
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    edited April 2010
    smacks forehead
    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.
  • Rob van Wijk
    Rob van Wijk 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.
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
  • Boneist
    Boneist 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
  • Rob van Wijk
    Rob van Wijk 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.
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Unless I'm missing something
    Hi Boneist,

    I think Tom already answered that for you ;)

    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! :)
  • Hoek
    Hoek 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 ;)
  • Rob van Wijk
    Rob van Wijk 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" ...
  • Hoek
    Hoek 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) ;)
  • Rob van Wijk
    Rob van Wijk 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.
  • Solomon Yakobson
    Solomon Yakobson 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.
  • Hoek
    Hoek 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
  • Abdul Halim
    Abdul Halim 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
    -----------------------ADD TWO ROWS--------------------------------
    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
  • Solomon Yakobson
    Solomon Yakobson 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.
  • Aketi Jyuuzou
    Aketi Jyuuzou 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
  • Abdul Halim
    Abdul Halim Member Posts: 105
    Thanks


    Regards
    Halim
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Rob van Wijk wrote:
    Those solutions are resetting every opening balance to 0...
    {noformat}*slaps forehead*{noformat}

    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
  • Karthick2003
    Karthick2003 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
           )
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    Better late than never! ;)
This discussion has been closed.