2 Replies Latest reply: May 3, 2013 10:06 AM by €\$ħ₪

# trying to follow Tabibitosan method

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
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...?