1 2 Previous Next 17 Replies Latest reply on Jul 18, 2008 12:05 PM by MichaelS Go to original post
      • 15. Re: Numbers range overlap
        94799
        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
          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
            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
            ORA-06550: line 2, column 33:
            PL/SQL: ORA-01729: database link name expected
            ORA-06550: line 2, column 18:
            PL/SQL: SQL Statement ignored
            1 2 Previous Next