2 Replies Latest reply: May 3, 2013 11:20 AM by Frank Kulash RSS

    trying to follow Tabibitosan method

    user13328581
      hello experts i am trying to follow this forum below.

      continious data range algorithm

      Unfortunately I do not get the logic used by BluShadow     

      SQL> ed
      Wrote file afiedt.buf
       
        1  with t as (select 1 as id, 1 as val1, 4 as val2 from dual union all
        2             select 1, 5, 6 from dual union all
        3             select 1, 7, null from dual union all
        4             select 2, 1, 3 from dual union all
        5             select 2, 4, 7 from dual union all
        6             select 2, 9, 12 from dual union all
        7             select 2, 13, null from dual union all
        8             select 3, 1, 3 from dual union all
        9             select 3, 4, null from dual
       10            )
       11  --
       12  select id
       13        ,val1 as "start"
       14        ,val2 as "end"
       15        ,case when nvl(lag(val2) over (partition by id order by val1),0) != val1-1 then 'hole or overlap' else null end as chk
       16  from t
       17* order by 1, 2
      SQL> / 
       
              ID      start        end CHK
      ---------- ---------- ---------- ---------------
               1          1          4
               1          5          6
               1          7
               2          1          3
               2          4          7
               2          9         12 hole or overlap
               2         13
               3          1          3
               3          4
       
      9 rows selected.
       
      SQL>
      why is there a hole for 9 and 12
      and not 7 and null...Can you please explain further.

      THank you
        • 1. Re: trying to follow Tabibitosan method
          Frank Kulash
          Hi,
          user13328581 wrote:
          hello experts i am trying to follow this forum below.

          continious data range algorithm

          Unfortunately I do not get the logic used by BluShadow     

          SQL> ed
          Wrote file afiedt.buf
          
          1  with t as (select 1 as id, 1 as val1, 4 as val2 from dual union all
          2             select 1, 5, 6 from dual union all
          3             select 1, 7, null from dual union all
          4             select 2, 1, 3 from dual union all
          5             select 2, 4, 7 from dual union all
          6             select 2, 9, 12 from dual union all
          7             select 2, 13, null from dual union all
          8             select 3, 1, 3 from dual union all
          9             select 3, 4, null from dual
          10            )
          11  --
          12  select id
          13        ,val1 as "start"
          14        ,val2 as "end"
          15        ,case when nvl(lag(val2) over (partition by id order by val1),0) != val1-1 then 'hole or overlap' else null end as chk
          16  from t
          17* order by 1, 2
          SQL> / 
          
          ID      start        end CHK
          ---------- ---------- ---------- ---------------
          1          1          4
          1          5          6
          1          7
          2          1          3
          2          4          7
          2          9         12 hole or overlap
          2         13
          3          1          3
          3          4
          
          9 rows selected.
          
          SQL>
          why is there a hole for 9 and 12
          and not 7 and null...Can you please explain further.
          That query has some text in the chk column if there is a hole or overlap before that row. (The LAG function looks at the previous row.)
          If you wanted to flag holes or overlaps after a given row, you could use LEAD insrtead of LAG.

          If there is a hole or overlap before row N, that implies there is a hole or overlap after row N-1, so all the relevant information is conveyed if you use just LAG to flag the later row. The code above does not attempt to flag the row before the hole or overlap; just the row after it.
          If you prefer having both rows flagged, then you can use both LAG and LEAD in the same query. (Try it, if you want to. What output would you want if there was a problem both before and after a given row: for example, if you added this:
          select 1 as id, 2 as val1, 20 as val2 from dual
          to the existing sample data?)
          • 2. Re: trying to follow Tabibitosan method
            €$ħ₪
            lag(val2) over (partition by id order by val1) previous value for 7 NULL is 6 since it belowngs to same ID which matches the val2 (end ) value.

            what else you are expecting...?