This content has been marked as final.
Show 17 replies

15. Re: Numbers range overlap
94799 Jul 18, 2008 11:07 AM (in response to Rob van Wijk)Thanks for your thoughts on this one
No problem. Thanks to all in fact, this has been an interesting thread. I hadn't caught up with Dave Aldridge's blog for a while (sorry Dave!). 
16. Re: Numbers range overlap
636120 Jul 18, 2008 11:51 AM (in response to 94799)Gents,
I thought the queries were equivalent but found different results though:
SQL> with
2 ranges as
3 (
4 select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
5 select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
6 select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
7 select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
8 select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
9 select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
10 select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual union all
11 select 8 as range_id, 'C' as external_key, 2 as val_from, 20 as val_to from dual union all
12 select 9 as range_id, 'C' as external_key, 10 as val_from, 10 as val_to from dual union all
13 select 10 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual union all
14 select 11 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual
15 )
16 select t1.*,t2.*
17 from ranges t1, ranges t2
18 where t1.external_key = t2.external_key
19 and t1.range_id != t2.range_id
20 and t1.val_to > t2.val_from
21 and t1.val_from < t2.val_to
22 /
RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO
       
4 A 3 9 1 A 1 5
4 A 3 9 2 A 8 10
1 A 1 5 4 A 3 9
2 A 8 10 4 A 3 9
9 C 10 10 8 C 2 20
8 C 2 20 9 C 10 10
6 rows selected
SQL> with
2 ranges as
3 (
4 select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
5 select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
6 select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
7 select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
8 select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
9 select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
10 select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual union all
11 select 8 as range_id, 'C' as external_key, 2 as val_from, 20 as val_to from dual union all
12 select 9 as range_id, 'C' as external_key, 10 as val_from, 10 as val_to from dual union all
13 select 10 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual union all
14 select 11 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual
15 )
16 select range_id, external_key, val_from, val_to
17 from (select range_id,
18 external_key,
19 val_from, val_to,
20 lead (val_from) over (partition by external_key order by val_from) lead_val_from,
21 lag (val_to) over (partition by external_key order by val_to) lag_val_to
22 from ranges)
23 where lead_val_from between val_from and val_to
24 or lag_val_to between val_from and val_to
25 /
RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO
   
1 A 1 5
4 A 3 9
2 A 8 10
8 C 2 20
10 D 1 1
11 D 1 1
6 rows selected
SQL> with
2 ranges as
3 (
4 select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
5 select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
6 select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
7 select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
8 select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
9 select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
10 select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual union all
11 select 8 as range_id, 'C' as external_key, 2 as val_from, 20 as val_to from dual union all
12 select 9 as range_id, 'C' as external_key, 10 as val_from, 10 as val_to from dual union all
13 select 10 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual union all
14 select 11 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual
15 )
16 select t1.*,t2.*
17 from ranges t1, ranges t2
18 where t1.external_key = t2.external_key
19 and t1.range_id != t2.range_id
20 and (sysdate+t1.val_from,sysdate+t1.val_to) overLaps
21 (sysdate+t2.val_from,sysdate+t2.val_to);
RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO
       
4 A 3 9 1 A 1 5
4 A 3 9 2 A 8 10
1 A 1 5 4 A 3 9
2 A 8 10 4 A 3 9
9 C 10 10 8 C 2 20
8 C 2 20 9 C 10 10
6 rows selected
SQL> with
2 ranges as
3 (
4 select 1 as range_id, 'A' as external_key, 1 as val_from, 5 as val_to from dual union all
5 select 2 as range_id, 'A' as external_key, 8 as val_from, 10 as val_to from dual union all
6 select 3 as range_id, 'A' as external_key, 12 as val_from, 15 as val_to from dual union all
7 select 4 as range_id, 'A' as external_key, 3 as val_from, 9 as val_to from dual union all
8 select 5 as range_id, 'B' as external_key, 2 as val_from, 12 as val_to from dual union all
9 select 6 as range_id, 'B' as external_key, 13 as val_from, 24 as val_to from dual union all
10 select 7 as range_id, 'B' as external_key, 25 as val_from, 50 as val_to from dual union all
11 select 8 as range_id, 'C' as external_key, 2 as val_from, 20 as val_to from dual union all
12 select 9 as range_id, 'C' as external_key, 10 as val_from, 10 as val_to from dual union all
13 select 10 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual union all
14 select 11 as range_id, 'D' as external_key, 1 as val_from, 1 as val_to from dual
15 )
16 select t1.*,t2.*
17 from ranges t1,ranges t2
18 where t1.external_key = t2.external_key
19 and t1.range_id != t2.range_id
20 and greatest(t1.val_from,t2.val_from) <= Least(t1.val_to,t2.val_to);
RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO RANGE_ID EXTERNAL_KEY VAL_FROM VAL_TO
       
4 A 3 9 1 A 1 5
4 A 3 9 2 A 8 10
1 A 1 5 4 A 3 9
2 A 8 10 4 A 3 9
9 C 10 10 8 C 2 20
8 C 2 20 9 C 10 10
11 D 1 1 10 D 1 1
10 D 1 1 11 D 1 1
8 rows selected 
17. Re: Numbers range overlap
MichaelS Jul 18, 2008 12:05 PM (in response to 94799)SQL> SELECT SYSDATE@! 2 FROM dual;
Just for info  looks like the plsql engine won't allow this syntax:
SQL> select sysdate@! s from dual S  18.07.08 1 row selected. SQL> var cur refcursor SQL> begin open :cur for select sysdate@! s from dual; end; / Error at line 5 ORA06550: line 2, column 33: PL/SQL: ORA01729: database link name expected ORA06550: line 2, column 18: PL/SQL: SQL Statement ignored