8 Replies Latest reply: Jun 20, 2008 6:37 PM by jgarry RSS

    PL/SQL

    599010
      Hi,
      I have a problem in finding duplicates in a table which has three fields : EVENTID, START_DATE, END_DATE. I want to find out another record which entirely or partially covers the period shown in START_DATE and END_DATE fields.
        • 1. Re: PL/SQL
          438877
          Like this ?
          SQL> with t as (
            2  select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
            3  union all
            4  select 2, trunc(sysdate)-4, trunc(sysdate) from dual
            5  union all
            6  select 3, trunc(sysdate)-4, trunc(sysdate)-3 from dual
            7  union all
            8  select 4, trunc(sysdate)-2, trunc(sysdate)-1 from dual
            9  union all
          10  select 5, trunc(sysdate)-2, trunc(sysdate) from dual
          11  union all
          12  select 6, trunc(sysdate), trunc(sysdate)+1 from dual
          13  )
          14  select * from t
          15  /

                  ID START_DAT END_DATE
          ---------- --------- ---------
                   1 17-SEP-07 19-SEP-07
                   2 16-SEP-07 20-SEP-07
                   3 16-SEP-07 17-SEP-07
                   4 18-SEP-07 19-SEP-07
                   5 18-SEP-07 20-SEP-07
                   6 20-SEP-07 21-SEP-07

          6 rows selected.

          SQL> with t as (
            2  select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
            3  union all
            4  select 2, trunc(sysdate)-4, trunc(sysdate) from dual
            5  union all
            6  select 3, trunc(sysdate)-4, trunc(sysdate)-3 from dual
            7  union all
            8  select 4, trunc(sysdate)-2, trunc(sysdate)-1 from dual
            9  union all
          10  select 5, trunc(sysdate)-2, trunc(sysdate) from dual
          11  union all
          12  select 6, trunc(sysdate), trunc(sysdate)+1 from dual
          13  )
          14  select t1.* from t, t t1
          15  where t.id = 1 and (t.start_date, t.end_date) overlaps (t1.start_date, t1.end_date)
          16  and t1.id <> t.id
          17  /

                  ID START_DAT END_DATE
          ---------- --------- ---------
                   2 16-SEP-07 20-SEP-07
                   4 18-SEP-07 19-SEP-07
                   5 18-SEP-07 20-SEP-07
          Rgds.
          • 2. Re: PL/SQL
            Rob van Wijk
            And if you don't want to use the undocumented OVERLAPS function, you can do it like this:
            SQL> create table mytable
              2  as
              3  select 1 eventid, date '2007-01-01' start_date, date '2007-12-31' end_date from dual union all
              4  select 2, date '2007-07-01', date '2008-07-01' from dual union all
              5  select 3, date '2006-07-01', date '2007-06-01' from dual union all
              6  select 4, date '2006-07-01', date '2008-07-01' from dual union all
              7  select 5, date '2006-01-01', date '2006-12-31' from dual union all
              8  select 6, date '2008-01-01', date '2008-12-31' from dual union all
              9  select 7, date '2007-04-01', date '2007-10-01' from dual
            10  /

            Tabel is aangemaakt.

            SQL> var P_EVENTID number
            SQL> exec :P_EVENTID := 1

            PL/SQL-procedure is geslaagd.

            SQL> with input as
              2  ( select eventid
              3         , start_date
              4         , end_date
              5      from mytable
              6     where eventid = :P_EVENTID
              7  )
              8  select t.eventid
              9       , t.start_date
            10       , t.end_date
            11    from mytable t
            12       , input
            13   where t.eventid != input.eventid
            14     and input.start_date < t.end_date
            15     and input.end_date > t.start_date
            16  /

               EVENTID START_DATE          END_DATE
            ---------- ------------------- -------------------
                     2 01-07-2007 00:00:00 01-07-2008 00:00:00
                     3 01-07-2006 00:00:00 01-06-2007 00:00:00
                     4 01-07-2006 00:00:00 01-07-2008 00:00:00
                     7 01-04-2007 00:00:00 01-10-2007 00:00:00

            4 rijen zijn geselecteerd.
            Regards,
            Rob.
            • 3. Re: PL/SQL
              Aketi Jyuuzou
              Sometimes,
              Result of OVERLAPS and result of simultaneous inequality are not equal.

              Although OVERLAPS of Oracle is unofficial.
              I hope that I know high specification of OVERLAPS of Oracle.

              for instance on 10.2.0.1.0
              SQL> select d1,d2,d3,d4,"OVERLAPS1","OVERLAPS2"
                2  from (select d1,d2,d3,d4,
                3        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
                4        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2"
                5        from (select Least(d1,d2) as d1,
                6              greatest(d1,d2) as d2,
                7              Least(d3,d4) as d3,
                8              greatest(d3,d4) as d4
                9              from(select
              10                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
              11                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
              12                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
              13                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
              14                   from all_catalog,all_catalog where RowNum <=90000)))
              15  where "OVERLAPS1" != "OVERLAPS2"
              16    and RowNum <=5;
              D1        D2        D3        D4        OVERLAPS1  OVERLAPS2
              --------  --------  --------  --------  ---------  ---------
              07-06-16  07-07-05  07-06-16  07-06-16          1          0
              07-06-28  07-12-02  07-06-28  07-06-28          1          0
              SQL> /
              D1        D2        D3        D4        OVERLAPS1  OVERLAPS2
              --------  --------  --------  --------  ---------  ---------
              07-10-11  07-11-19  07-10-11  07-10-11          1          0
              07-08-15  07-08-25  07-08-15  07-08-15          1          0
              07-06-13  07-10-25  07-06-13  07-06-13          1          0
              07-08-09  07-12-03  07-08-09  07-08-09          1          0
              07-07-10  07-11-26  07-07-10  07-07-10          1          0
              • 4. Re: PL/SQL
                Aketi Jyuuzou
                umm
                SQL> select
                  2  count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
                  3  count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
                  4  count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
                  5  count(nullif("OVERLAPS1","OVERLAPS5")) as OL5
                  6  from (select d1,d2,d3,d4,
                  7        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
                  8        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
                  9        case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
                10        case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
                11        case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5"
                12        from (select Least(d1,d2) as d1,
                13              greatest(d1,d2) as d2,
                14              Least(d3,d4) as d3,
                15              greatest(d3,d4) as d4
                16              from(select
                17                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
                18                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
                19                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
                20                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
                21                   from all_catalog,all_catalog where RowNum <=90000)));
                 OL2   OL3   OL4   OL5
                ----  ----  ----  ----
                   1   293   307   599
                SQL> /
                 OL2   OL3   OL4   OL5
                ----  ----  ----  ----
                   2   282   303   583
                SQL> /
                 OL2   OL3   OL4   OL5
                ----  ----  ----  ----
                   2   305   291   594                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
                • 5. Reserch OVERLAPS predicate on Oracle
                  Aketi Jyuuzou
                  I researched.
                  SQL> select count(*),
                    2  count(nullif("OVERLAPS1","OVERLAPS2")) as OL2,
                    3  count(nullif("OVERLAPS1","OVERLAPS3")) as OL3,
                    4  count(nullif("OVERLAPS1","OVERLAPS4")) as OL4,
                    5  count(nullif("OVERLAPS1","OVERLAPS5")) as OL5,
                    6  count(nullif("OVERLAPS1","OVERLAPS6")) as OL6
                    7  from (select d1,d2,d3,d4,
                    8        case when (d1,d2) OVERLAPS (d3,d4) then 1 else 0 end as "OVERLAPS1",
                    9        case when d3 < d2 and d1 < d4 then 1 else 0 end as "OVERLAPS2",
                  10        case when d3 < d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS3",
                  11        case when d3 <= d2 and d1 < d4 then 1 else 0 end as "OVERLAPS4",
                  12        case when d3 <= d2 and d1 <= d4 then 1 else 0 end as "OVERLAPS5",
                  13        case when d3 < d2 and d1 < d4
                  14               or d1=all(d3,d4) then 1 else 0 end as "OVERLAPS6"
                  15        from (select Least(d1,d2) as d1,
                  16              greatest(d1,d2) as d2,
                  17              Least(d3,d4) as d3,
                  18              greatest(d3,d4) as d4
                  19              from(select
                  20                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D1,
                  21                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D2,
                  22                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D3,
                  23                   trunc(SYSDATE+mod(dbms_random.random(),100)) as D4
                  24                   from all_catalog,all_catalog where RowNum <=90000)));
                   COUNT(*)        OL2        OL3        OL4        OL5        OL6
                  ---------  ---------  ---------  ---------  ---------  ---------
                      90000          2        291        317        606          0
                  SQL> /
                   COUNT(*)        OL2        OL3        OL4        OL5        OL6
                  ---------  ---------  ---------  ---------  ---------  ---------
                      90000          3        320        299        616          0
                  SQL> /
                   COUNT(*)        OL2        OL3        OL4        OL5        OL6
                  ---------  ---------  ---------  ---------  ---------  ---------
                      90000          4        302        300        598          0
                  SQL> /
                   COUNT(*)        OL2        OL3        OL4        OL5        OL6
                  ---------  ---------  ---------  ---------  ---------  ---------
                      90000          2        308        317        623          0
                  Therefore I conclude that "(d1,d2) OVERLAPS (d3,d4)" means "d3 < d2 and d1 < d4 or d1=all(d3,d4)"
                  • 6. Re: Reserch OVERLAPS predicate on Oracle
                    Aketi Jyuuzou
                    using analytical function to calculate concurrency between date range

                    OVERLAPS is not documented.
                    However on oracle11g,
                    WM_OVERLAPS is documented.
                    • 7. SQL
                      Mohan K Narayanan
                      Hi friends,
                      I am using a database ORACLE 10G. I wrote a query using with function but it can't ran and it shows error message is

                      SQL*Plus internal error state 2091, context 0:0:0
                      Unsafe to proceed


                      Query is

                      1 with t as
                      2 (select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
                      3 union all
                      4 select 2 ,trunc(sysdate)-4 ,trunc(sysdate) from dual
                      5 union all
                      6 select 3 , trunc(sysdate)-4,trunc(sysdate)-3 from dual
                      7 union all
                      8 select 4 ,trunc(sysdate)-2,trunc(sysdate)-1 from dual
                      9 )
                      10* select * from t
                      11 /
                      • 8. Re: SQL
                        jgarry
                        Worked for me, 10.2.0.4:
                          1  with t as
                          2  (select 1 id, trunc(sysdate)-3 start_date, trunc(sysdate-1) end_date from dual
                          3  union all
                          4  select 2 ,trunc(sysdate)-4 ,trunc(sysdate) from dual
                          5  union all
                          6  select 3 , trunc(sysdate)-4,trunc(sysdate)-3 from dual
                          7  union all
                          8  select 4 ,trunc(sysdate)-2,trunc(sysdate)-1 from dual
                          9  )
                         10* select * from t
                        SQL> /
                        
                                ID START_DAT END_DATE
                        ---------- --------- ---------
                                 1 17-JUN-08 19-JUN-08
                                 2 16-JUN-08 20-JUN-08
                                 3 16-JUN-08 17-JUN-08
                                 4 18-JUN-08 19-JUN-08