14 Replies Latest reply: May 19, 2012 4:42 PM by chris227 RSS

    Rows Unbounded Preceding Clause - Difficult Query Case

    SigCle
      Dear SQL'er Expert,

      Very need SQL Expert Logical.



      create table mr_awal_btps(tx number, saldo_sub number, saldo_main number);

      truncate table mr_awal_btps;

      insert into mr_awal_btps
      select 10000,4000,10000 from dual
      union all
      select 5000, 3000, 0 from dual
      union all
      select 4000, 3000, 0 from dual
      union all
      select 3000,1000,0 from dual
      union all
      select 2000,1500,0 from dual
      union all
      select 3000,1500,0 from dual
      union all
      select 2000,1500,0 from dual

      commit;

      select * from mr_awal_btps;


      -- there is a problem like this :
      -- if sub < tx then borrow to main saldo, and main saldo minus by lack
      -- however, if tx - sub > remain main saldo then current row will be skip and proceed to next row
      -- So, last result should be :


      INS     SUB     MAIN    REMAIN_MAIN_saldo

      *10000     4000     10000     4000*
      *5000     3000     0     2000*
      *4000     3000     0     1000*
      *3000     1000     0     1000 --> stay to 1000 because ins - sub = 3000 - 1000 = 2000 and 2000 > remain main saldo*
      *2000     1500     0     500*
      *3000     1500     0     500 --> stay to 1000 because ins - sub = 3000 - 1500 = 1500 and 1500 > remain main saldo*
      *2000     1500     0     0*



      --------------------------------------------------------------------------------------------------------------------
      this my last query which still uncomplete :
      ------------------------------------------


      select
      x5.*,
      case when lag(flag) over (order by rownum) = 1 and flag = 0 then 0 else 1 end
      from
      (
      select
      x4.*,
      case when lag(t1,rownum-1) over (order by rownum) + t2 > 0 then 1 else 0 end flag
      from
      (
      select
      x3.*
      from
      (
      select
      x2.*,
      sum(case when rownum = 1 then 0 else lack end) over (order by rownum rows unbounded preceding) t2
      from
      (
      select
      x.*,
      (sub - ins) lack,
      case when (main + sub - ins) < 0 then 0 else (main + sub - ins) end t1
      from
      mr_awal_btps x
      ) x2
      ) x3
      ) x4
      ) x5



      Is there any row to embed where clause form in Rows Unbounded Preceding Clause ?


      Thx so much.
      Any help will be appreciated.
        • 1. Re: Rows Unbounded Preceding Clause - Difficult Query Case
          Frank Kulash
          Hi,
          SigCle wrote:
          ... create table mr_awal_btps(tx number, saldo_sub number, saldo_main number); ...
          -- there is a problem like this :
          -- if sub < tx then borrow to main saldo, and main saldo minus by lack
          -- however, if tx - sub > remain main saldo then current row will be skip and proceed to next row
          How do you know which row is the next row? Words like "next" and "preceding" only have meaning when there is some order. Do you have another column tht determines the order?
          -- So, last result should be :


          INS SUB MAIN    REMAIN_MAIN_saldo

          *10000 4000 10000 4000*
          *5000 3000 0 2000*
          *4000 3000 0 1000*
          *3000 1000 0 1000 --> stay to 1000 because ins - sub = 3000 - 1000 = 2000 and 2000 > remain main saldo*
          *2000 1500 0 500*
          *3000 1500 0 500 --> stay to 1000 because ins - sub = 3000 - 1500 = 1500 and 1500 > remain main saldo*
          *2000 1500 0 0*
          It looks like, after the first row, there is no way to get the value of remian_main_saldo without knowing the value of the previous remain_main_saldo. Analytic functions can't do that.

          What version of Oracle are you using?
          If you have Oracle 11.2, you can use a recursive WITH clause.
          In Oracle 10.1 (and higher) you can use MODEL.
          In Oracle 9, you can probably do something with SYS_CONNECT_BY_PATH and a user-defined function.
          this my last query which still uncomplete : ...
          That approach will only work if you know an upper boiund to the number of rows in the result set, and it requires an extra sub-query for each additional row. A recursive WITH clause could do basically the same thing for any number of rows, without hard-coding more sub-queries for more rows.
          • 2. Re: Rows Unbounded Preceding Clause - Difficult Query Case
            indra budiantho
            same on me, trying, but cannot complete :(
            with t as(
            select 10000 tx,4000 saldo_sub,10000 saldo_main from dual
            union all
            select 5000, 3000, 0 from dual
            union all
            select 4000, 3000, 0 from dual
            union all
            select 3000,1000,0 from dual
            union all
            select 2000,1500,0 from dual
            union all
            select 3000,1500,0 from dual
            union all
            select 2000,1500,0 from dual
            ),
            mm as(
            select y.*
            from(
            select x.*,x.saldo_main-x.tx_sub rms
            from(
            select t.*, tx-saldo_sub tx_sub
             from t
            ) x
            ) y
            )
            select pp.*, sum(rms) OVER (ORDER BY rownum ROWS UNBOUNDED PRECEDING) REMAIN_MAIN_saldo
            from 
            (select rownum id,mm.*
            from mm) pp
            ID     TX     SALDO_SUB     SALDO_MAIN     TX_SUB     RMS     REMAIN_MAIN_SALDO

            1     10000     4000     10000     6000     4000     4000
            2     5000     3000     0     2000     -2000     2000
            3     4000     3000     0     1000     -1000     1000
            4     3000     1000     0     2000     -2000     -1000
            5     2000     1500     0     500     -500     -1500
            6     3000     1500     0     1500     -1500     -3000
            7     2000     1500     0     500     -500     -3500

            Edited by: Angry Oracle on May 15, 2012 6:34 AM
            • 3. Re: Rows Unbounded Preceding Clause - Difficult Query Case
              Hoek
              This sentence I cannot understand:
              -- if sub < tx then borrow to main saldo, and main saldo minus by lack
              Can you explain in more detail what that means?
              Also, why is remain_main_saldo 4000 for the first record (tx= 10000)?
              • 4. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                chris227
                Hi,

                since you have so many unresolved question, its seems hardly possible to find a correct answer to this one.
                I will try anyway.
                There is missing an order over your rows, so i added a column rn for this.
                In the first row 4000 as sub seemed wrong to me os i used 6000 instead.
                with testdata as (
                select 1 rn, 10000 ins,6000 sub,10000 X from dual
                union all
                select 2, 5000, 3000, 0 from dual
                union all
                select 3, 4000, 3000, 0 from dual
                union all
                select 4, 3000,1000,0 from dual
                union all
                select 5, 2000,1500,0 from dual
                union all
                select 6, 3000,1500,0 from dual
                union all
                select 7, 2000,1500,0 from dual
                )
                
                select
                rn, ins, sub,
                case
                  when saldo > saldo_lag
                  then saldo_lag
                  else 
                  lag(saldo,1,2*saldo) over(partition by (case when saldo > saldo_lag then 1 else 0 end) order by rn)
                  - saldo
                end saldo
                from (
                select
                  rn, ins, sub,
                  ins-sub saldo,
                  lag(ins-sub,1,ins-sub) over(order by rn) saldo_lag  
                from testdata
                )
                order by rn
                
                RN     INS     SUB     SALDO
                1     10000     6000     4000
                2     5000     3000     2000
                3     4000     3000     1000
                4     3000     1000     1000
                5     2000     1500     500
                6     3000     1500     500
                7     2000     1500     0
                regards

                Edited by: chris227 on 15.05.2012 07:22
                defaul 2*saldo in lag
                • 5. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                  SigCle
                  rownum tx SUB_SALDO MAIN_SALDO REMAIN_OF_MAIN_SALDO Explanation
                  -----

                  1 10000 4000 10000 4000 (if sub_saldo < ins then there's lack saldo --> 4000 (sub) - 10000 (tx) = -6000 (as lack),
                  so borrow from main_saldo -->
                  check : if lack(6000) < current_main_saldo(10000) then
                  10000(current_main_saldo) - 6000(lack) = *4000 (as current remain main saldo)*

                  2 5000 3000 0 2000 (if sub_saldo < ins then there's lack saldo --> 3000 (sub) - 5000 (tx) = -2000 (as lack),
                  so borrow from main_saldo -->
                  check : if lack(2000) < current_main_saldo(4000) then
                  4000(current_main_saldo) - 2000(lack) = *2000 (as current remain main saldo)*

                  3 4000 3000 0 1000 (if sub_saldo < ins then there's lack saldo --> 3000 (sub) - 4000 (tx) = -1000 (as lack),
                  so borrow from main_saldo -->
                  check : if lack (1000) < current_main_saldo (2000) then
                  2000(current_main_saldo) - 1000(lack) = *1000 (as current remain main saldo)*

                  4 3000 1000 0 1000 --> (if sub_saldo < ins then there's lack saldo --> 1000 (sub) - 3000 (tx) = -2000 (as lack),
                  so borrow from main_saldo -->
                  in this step :
                  lack (2000) > current_main_saldo (1000)
                  so current_main_saldo stay at 1000

                  5 2000 1500 0 500 (if sub_saldo < ins then there's lack saldo --> 1500 (sub) - 2000 (tx) = -500 (as lack),
                  so borrow from main_saldo -->
                  check : if lack (500) < current_main_saldo (1000) then
                  1000(current_main_saldo) - 500(lack) = *500 (as current remain main saldo)*

                  6 3000 1500 0 500 --> (if sub_saldo < ins then there's lack saldo --> 1500 (sub) - 3000 (tx) = -1500 (as lack),
                  so borrow from main_saldo -->
                  in this step :
                  lack (1500) > current_main_saldo (500)
                  so current_main_saldo stay at 500

                  7 2000 1500 0 0 (if sub_saldo < ins then there's lack saldo --> 1500 (sub) - 2000 (tx) = -500 (as lack),
                  so borrow from main_saldo -->
                  check : if lack (500) < current_main_saldo (500) then
                  500(current_main_saldo) - 500(lack) = *0 (as current remain main saldo)*


                  Oracle Version 10.2.0.4 running on HP-UX

                  Sorry before on my current mess-post format

                  Edited by: SigCle on May 16, 2012 4:02 AM

                  Edited by: SigCle on May 16, 2012 12:05 PM

                  Edited by: SigCle on May 16, 2012 12:07 PM
                  • 6. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                    SigCle
                    Regards Mr. Frank Kulash,

                    I have explain Oracle Version and explanation of my question.

                    Is there a way such :

                    sum(col1 ignore (case when col1 > 100 then col1 else 0 end)) over (order by rownum) ?


                    Please help.

                    Best Regards

                    Edited by: SigCle on May 16, 2012 4:32 AM
                    • 7. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                      SigCle
                      Regards Mr. Chris,

                      Your query is so good, but it still didn't representate my question.
                      Thanks before.

                      Best Regards
                      • 8. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                        696547
                        WITH t AS
                          (SELECT 10000 TX,4000 saldo_sub ,10000 saldo_main FROM dual
                          UNION ALL
                          SELECT 5000, 3000, 0 FROM dual
                          UNION ALL
                          SELECT 4000, 3000, 0 FROM dual
                          UNION ALL
                          SELECT 3000,1000,0 FROM dual
                          UNION ALL
                          SELECT 2000,1500,0 FROM dual
                          UNION ALL
                          SELECT 3000,1500,0 FROM dual
                          UNION ALL
                          SELECT 2000,1500,0 FROM dual
                          )
                        SELECT tx,
                          saldo_sub,
                          saldo_main,
                          DECODE(rownum, 1, c1, 2, c2, 3, c3, 4, c4, 5, c5, 6, c6, 7, c7) current_saldo_main
                        FROM
                          (SELECT tx,
                            saldo_sub,
                            saldo_main,
                            c1,
                            c2,
                            c3,
                            c4,
                            c5,
                            c6,
                            CASE
                              WHEN (TX                             - saldo_sub) <= lag(c6) OVER (ORDER BY ROWNUM)
                              THEN (lag(c6) over (order by rownum) + c1)
                              ELSE lag(c6) OVER (ORDER BY ROWNUM)
                            END c7
                          FROM
                            (SELECT tx,
                              saldo_sub,
                              saldo_main,
                              c1,
                              c2,
                              c3,
                              c4,
                              c5,
                              CASE
                                WHEN (TX                             - saldo_sub) <= lag(c5) OVER (ORDER BY ROWNUM)
                                THEN (lag(c5) over (order by rownum) + c1)
                                ELSE lag(c5) over (order by rownum)
                              END c6
                            FROM
                              (SELECT tx,
                                saldo_sub,
                                saldo_main,
                                c1,
                                c2,
                                c3,
                                c4,
                                CASE
                                  WHEN (TX                             - saldo_sub) <= lag(c4) OVER (ORDER BY ROWNUM)
                                  THEN (lag(c4) OVER (ORDER BY ROWNUM) + c1)
                                  ELSE lag(c4) over (order by rownum)
                                END c5
                              FROM
                                (SELECT tx,
                                  saldo_sub,
                                  saldo_main,
                                  c1,
                                  c2,
                                  c3,
                                  CASE
                                    WHEN (tx                             - saldo_sub) <= lag(c3) over (order by rownum)
                                    THEN (lag(c3) OVER (ORDER BY ROWNUM) + c1)
                                    ELSE lag(c3) over (order by rownum)
                                  END c4
                                FROM
                                  (SELECT tx,
                                    saldo_sub,
                                    saldo_main,
                                    c1,
                                    c2,
                                    CASE
                                      WHEN (TX                             - saldo_sub) <= lag(c2) OVER (ORDER BY ROWNUM)
                                      THEN (lag(c2) OVER (ORDER BY ROWNUM) + c1)
                                      ELSE lag(c2) over (order by rownum)
                                    END c3
                                  FROM
                                    (SELECT tx,
                                      saldo_sub,
                                      saldo_main,
                                      (saldo_main    + saldo_sub - TX) c1,
                                      lag(saldo_main + saldo_sub - TX) OVER (ORDER BY ROWNUM) + (saldo_main + saldo_sub - TX) c2
                                    FROM t
                                    )
                                  )
                                )
                              )
                            )
                          );
                        
                        --Output
                        
                        10000     4000     10000     4000
                        5000     3000     0     2000
                        4000     3000     0     1000
                        3000     1000     0     1000
                        2000     1500     0     500
                        3000     1500     0     500
                        2000     1500     0     0
                        • 9. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                          chris227
                          Is there a way such :

                          sum(col1 ignore (case when col1 > 100 then col1 else 0 end)) over (order by rownum) ?
                          Yes, i have shown you this already
                          case
                            when col1 > 100
                            then 0
                            else 
                            sum(col1) over(partition by (case whencol1 <= 100 then 1 else 0 end) order by rn)
                            - saldo
                          end saldo
                          One of the biggest problem in your query is the usage of rownum. Since there is never an order by on which the rownum is based, your results become unpredictable. The order by inside the window clause dooes not do this, because it works only on the specified partition and is applied somewhat latter.
                          • 10. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                            SigCle
                            Dear Ankit Rathi ,

                            Yes, your answer is true for 7 input rows and using right validation method as I expected.
                            But, the problem is the input rows is running dynamically so it could be 7 rows could be 9 rows or maybe 1000 rows, depend on data growth.
                            Anyway, thx so much for the answer.

                            Best Regards,
                            LinXianHan 林贤汉
                            • 11. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                              SigCle
                              Dear Mr. Chris,

                              Obviously it is order by TX or Ins on my earliest post.

                              Best Regards,
                              林贤汉
                              • 12. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                                chris227
                                next time be more detailed with your requirements please ;-)
                                with testdata as (
                                select 1 rn, 10000 ins, 4000 sub, 10000 cms from dual
                                union all
                                select 2, 5000, 3000, 0 from dual
                                union all
                                select 3, 4000, 3000, 0 from dual
                                union all
                                select 4, 3000,1000,0 from dual
                                union all
                                select 5, 2000,1500,0 from dual
                                union all
                                select 6, 3000,1500,0 from dual
                                union all
                                select 7, 2000,1500,0 from dual
                                )
                                 
                                select rn, ins, sub, saldo from testdata
                                model
                                dimension by (rn)
                                measures( ins, sub, cms, 0 saldo)
                                rules (
                                saldo[1]=cms[1] + sub[1] - ins[1],
                                saldo[rn>1]order by rn=
                                case when
                                saldo[cv()-1] + sub[cv()] - ins[cv()] < 0
                                then saldo[cv()-1]
                                else
                                saldo[cv()-1] +sub[cv()] - ins[cv()]
                                end
                                )
                                order by rn
                                
                                RN     INS     SUB     SALDO
                                1     10000     4000     4000
                                2     5000     3000     2000
                                3     4000     3000     1000
                                4     3000     1000     1000
                                5     2000     1500     500
                                6     3000     1500     500
                                7     2000     1500     0
                                regards

                                Edited by: chris227 on 16.05.2012 02:21
                                changed on rownum generation based on reply by OP before

                                Edited by: chris227 on 19.05.2012 14:37
                                corrected, old version with hardcoded rownum
                                • 13. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                                  chris227
                                  by careful on the usage of rownum and ordering. look this:
                                  with data as (
                                  select 10000 ins,4000 sub,10000 cms from dual
                                  union all
                                  select 4000, 3000, 0 from dual
                                  union all
                                  select 3000,1000,0 from dual
                                  union all
                                  select 2000,1500,0 from dual
                                  union all
                                  select 5000, 3000, 0 from dual
                                  union all
                                  select 3000,1500,0 from dual
                                  union all
                                  select 2000,1500,0 from dual
                                  )
                                  select rownum,ins,sub,cms
                                  from data
                                  
                                  ROWNUM     INS     SUB     CMS
                                  1     10000     4000     10000
                                  2     4000     3000     0
                                  3     3000     1000     0
                                  4     2000     1500     0
                                  5     5000     3000     0
                                  6     3000     1500     0
                                  7     2000     1500     0
                                  7 rows returned in 0.01 seconds                  
                                  
                                  The 500 is on place 5 as in the with-clause
                                  
                                  with data as (
                                  select 10000 ins,4000 sub,10000 cms from dual
                                  union all
                                  select 4000, 3000, 0 from dual
                                  union all
                                  select 3000,1000,0 from dual
                                  union all
                                  select 2000,1500,0 from dual
                                  union all
                                  select 5000, 3000, 0 from dual
                                  union all
                                  select 3000,1500,0 from dual
                                  union all
                                  select 2000,1500,0 from dual
                                  )
                                  select rownum,ins,sub,cms
                                  from data
                                  order by ins desc
                                  
                                  ROWNUM     INS     SUB     CMS
                                  1     10000     4000     10000
                                  5     5000     3000     0
                                  2     4000     3000     0
                                  6     3000     1500     0
                                  3     3000     1000     0
                                  7     2000     1500     0
                                  4     2000     1500     0
                                  
                                  rownum is not in order, because the order is applied a f t e r the rownum generation.
                                  
                                  so 
                                  
                                  select
                                  row_number() over(order by ins desc) rn
                                  ,ins,sub,cms
                                  from data
                                  
                                  RN     INS     SUB     CMS
                                  1     10000     4000     10000
                                  2     5000     3000     0
                                  3     4000     3000     0
                                  4     3000     1500     0
                                  5     3000     1000     0
                                  6     2000     1500     0
                                  7     2000     1500     0
                                  
                                  does the correct rownum assignment
                                  regards
                                  • 14. Re: Rows Unbounded Preceding Clause - Difficult Query Case
                                    chris227
                                    SigCle wrote:
                                    Dear Mr. Chris,

                                    Obviously it is order by TX or Ins on my earliest post.
                                    Obviously it is not. If it would be like this, the solution is different, because the 3000s come before the 2000s.
                                    with data as (
                                    select 10000 ins,4000 sub,10000 cms from dual
                                    union all
                                    select 5000, 3000, 0 from dual
                                    union all
                                    select 4000, 3000, 0 from dual
                                    union all
                                    select 3000,1000,0 from dual
                                    union all
                                    select 2000,1500,0 from dual
                                    union all
                                    select 3000,1500,0 from dual
                                    union all
                                    select 2000,1500,0 from dual
                                    )
                                    , testdata as (
                                    select 
                                    ins, sub, cms
                                    ,row_number() over(order by ins desc) rn
                                    from data
                                    )
                                     
                                    select rn, ins, sub, saldo from testdata
                                    model
                                    dimension by (rn)
                                    measures( ins, sub, cms,0 saldo)
                                    rules (
                                    saldo[1]=cms[1] + sub[1] - ins[1],
                                    saldo[rn>1]order by rn=
                                    case when
                                    saldo[cv()-1] + sub[cv()] - ins[cv()] < 0
                                    then saldo[cv()-1]
                                    else
                                    saldo[cv()-1] +sub[cv()] - ins[cv()]
                                    end
                                    )
                                    order by rn
                                    
                                    RN     INS     SUB     SALDO
                                    1     10000     4000     4000
                                    2     5000     3000     2000
                                    3     4000     3000     1000
                                    4     3000     1000     1000
                                    5     3000     1500     1000
                                    6     2000     1500     500
                                    7     2000     1500     0
                                    So as i assumed at the beginning, another unsolved question of yours, just to challenging ...