This discussion is archived
14 Replies Latest reply: May 19, 2012 2:42 PM by chris227 RSS

Rows Unbounded Preceding Clause - Difficult Query Case

SigCle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points