This discussion is archived
10 Replies Latest reply: Apr 10, 2011 7:01 PM by 672680 RSS

How to use CONNECT BY LEVEL to get this result?

672680 Pro
Currently Being Moderated
Hi all,

my DB version is 10gR2.

here is the table and the data:
create table t (from_dt date, to_dt date, eno varchar2(2), deptno varchar2(10));

insert into T values (TO_DATE('2010-01-01','yyyy-mm-dd'),TO_DATE('2010-04-30','yyyy-mm-dd'),'A','DEPTA');
insert into T valueS (TO_DATE('2010-05-01','yyyy-mm-dd'),TO_DATE('2010-12-31','yyyy-mm-dd'),'A','DEPTB');
insert into T values (TO_DATE('2010-01-01','yyyy-mm-dd'),TO_DATE('2010-06-30','yyyy-mm-dd'),'B','DEPTA');
insert into T valueS (TO_DATE('2010-07-01','yyyy-mm-dd'),TO_DATE('2010-12-31','yyyy-mm-dd'),'B','DEPTB');

from_dt       to_dt           ENO   DEPTNO
------------------------------------------------
2010-01-01 2010-04-30  A     DEPTA
2010-05-01 2010-12-31  A     DEPTB
2010-01-01 2010-06-30  B     DEPTA
2010-07-01 2010-12-31  B     DEPTB
Here is the result I want:
MONTH    ENO   DEPTNO
-----------------------------
2010-01    A      DEPTA
2010-02    A      DEPTA
2010-03    A      DEPTA
2010-04    A      DEPTA
2010-05    A      DEPTB
...............
Here is what I came up with:
select distinct ADD_MONTHS(TRUNC(FROM_DT,'MON'),level-1) as MONTHS, 
       ENO, 
       DEPTNO
from (
    select FROM_DT, TO_DT, ENO, DEPTNO, ROUND(MONTHS_BETWEEN(TO_DT, FROM_DT)) as BET from T
) connect by level<=BET
order by eno, deptno,months ;
the problem with this is that, many duplicate rows are generated if I remove DISTINCT from the sql.
and I don`t know how that happens? How CONNECT BY LEVEL, returns me so many duplicate rows?

Anyone have any idea about this?
And is there any other way to do this as well?

Thanks
  • 1. Re: How to use CONNECT BY LEVEL to get this result?
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    In this case,model clause is one way :-)
    with t(from_dt,to_dt,eno,deptno) as(
    select date '2010-01-01',date '2010-04-30','A','DEPTA' from dual union
    select date '2010-05-01',date '2010-12-31','A','DEPTB' from dual union
    select date '2010-01-01',date '2010-06-30','B','DEPTA' from dual union
    select date '2010-07-01',date '2010-12-31','B','DEPTB' from dual)
    select *
      from t
     model
    partition by(RowNum as PID)
    dimension by(0 as soeji)
    measures(from_dt,to_dt,eno,deptno)
    rules iterate(100)
    UNTIL (add_months(from_dt[0],ITERATION_NUMBER+1) > to_dt[0])
    (from_dt[ITERATION_NUMBER]=add_months(from_dt[0],ITERATION_NUMBER),
     eno[ITERATION_NUMBER]=eno[0],
     deptno[ITERATION_NUMBER]=deptno[0])
    order by eno,from_dt;
    
    PID  SOEJI  FROM_DT   TO_DT     E  DEPTN
    ---  -----  --------  --------  -  -----
      1      0  10-01-01  10-04-30  A  DEPTA
      1      1  10-02-01  null      A  DEPTA
      1      2  10-03-01  null      A  DEPTA
      1      3  10-04-01  null      A  DEPTA
      3      0  10-05-01  10-12-31  A  DEPTB
      3      1  10-06-01  null      A  DEPTB
      3      2  10-07-01  null      A  DEPTB
      3      3  10-08-01  null      A  DEPTB
      3      4  10-09-01  null      A  DEPTB
      3      5  10-10-01  null      A  DEPTB
      3      6  10-11-01  null      A  DEPTB
      3      7  10-12-01  null      A  DEPTB
      2      0  10-01-01  10-06-30  B  DEPTA
      2      1  10-02-01  null      B  DEPTA
      2      2  10-03-01  null      B  DEPTA
      2      3  10-04-01  null      B  DEPTA
      2      4  10-05-01  null      B  DEPTA
      2      5  10-06-01  null      B  DEPTA
      4      0  10-07-01  10-12-31  B  DEPTB
      4      1  10-08-01  null      B  DEPTB
      4      2  10-09-01  null      B  DEPTB
      4      3  10-10-01  null      B  DEPTB
      4      4  10-11-01  null      B  DEPTB
      4      5  10-12-01  null      B  DEPTB
  • 2. Re: How to use CONNECT BY LEVEL to get this result?
    Aketi Jyuuzou Oracle ACE
    Currently Being Moderated
    And funny solution :8}
    select ADD_MONTHS(TRUNC(FROM_DT,'MON'),level-1) as MONTHS, 
           ENO, 
           DEPTNO
    from (
        select RowNum as rn,FROM_DT, TO_DT, ENO, DEPTNO,
        ROUND(MONTHS_BETWEEN(TO_DT, FROM_DT)) as BET from T
    ) connect by level<=BET
          and prior rn = rn
          and prior dbms_random.value != 1
    order by eno, deptno,months ;
  • 3. Re: How to use CONNECT BY LEVEL to get this result?
    William Robertson Oracle ACE
    Currently Being Moderated
    Off topic perhaps, but there's nothing special about LEVEL - CONNECT BY can use any condition.
    SQL> select rownum from dual connect by dbms_random.value(1,10) < 5;
    
        ROWNUM
    ----------
             1
             2
    
    2 rows selected.
    
    SQL> select rownum from dual connect by dbms_random.value(1,10) < 5;
    
        ROWNUM
    ----------
             1
             2
             3
             4
             5
    
    5 rows selected.
    I personally find ROWNUM expressions more intuitive with CONNECT BY, although it comes to the same thing.
  • 4. Re: How to use CONNECT BY LEVEL to get this result?
    672680 Pro
    Currently Being Moderated
    Hi William,
    I found that there is huge difference between ROWNUM and LEVEL, when used in below sql.
    by huge difference, I mean, the result is quite different and none of them is giving me what I want.

    here is with ROWNUM:
    select ADD_MONTHS(TRUNC(FROM_DT,'MON'),ROWNUM-1) as MONTHS, 
           ENO, 
           DEPTNO
    from (
        select FROM_DT, TO_DT, ENO, DEPTNO, ROUND(MONTHS_BETWEEN(TO_DT, FROM_DT)) as BET from T
    ) connect by ROWNUM<=BET
    order by eno, deptno,months ;
    
    MONTHS         ENO   DEPTNO
    ---------------------------------------
    01-OCT-10     A     DEPTA
    01-NOV-10     A     DEPTB
    01-DEC-10     A     DEPTB
    01-MAR-11     A     DEPTB
    01-JAN-10     B     DEPTA
    01-FEB-10     B     DEPTA
    01-MAR-10     B     DEPTA
    01-APR-10     B     DEPTA
    01-MAY-10     B     DEPTA
    01-JUN-10     B     DEPTA
    01-MAR-11     B     DEPTB
    Here with LEVEL:
    select ADD_MONTHS(TRUNC(FROM_DT,'MON'),level-1) as MONTHS, 
           ENO, 
           DEPTNO
    from (
        select FROM_DT, TO_DT, ENO, DEPTNO, ROUND(MONTHS_BETWEEN(TO_DT, FROM_DT)) as BET from T
    ) connect by level<=BET
    order by eno, deptno,months ;
    
    MONTHS      ENO     DEPTNO
    -------------------------
    01-APR-10     A     DEPTA
    01-APR-10     A     DEPTA
    01-APR-10     A     DEPTA
    01-APR-10     A     DEPTA
    01-APR-10     A     DEPTA
    01-APR-10     A     DEPTA
    ......
    What I really want to know is how LEVEL product that much records?

    Thanks
  • 5. Re: How to use CONNECT BY LEVEL to get this result?
    MichaelS Guru
    Currently Being Moderated
    And is there any other way to do this as well?
    Yes there is:
    SQL> with t  as (
     select date '2010-01-01' from_dt,date '2010-04-30' to_dt,'A' eno,'DEPTA' deptno from dual union
     select date '2010-05-01',date '2010-12-31','A','DEPTB' from dual union
     select date '2010-01-01',date '2010-06-30','B','DEPTA' from dual union
     select date '2010-07-01',date '2010-12-31','B','DEPTB' from dual
    )
    --
    --
    select from_dt + to_number(column_value) - 1 months, eno, deptno
      from t,
           xmltable ('for $i in 1 to xs:int(.) return $i'
                     passing xmlelement(e, round (months_between (to_dt, from_dt))))
    order by eno, deptno, months
    /
    MONTHS     ENO DEPTNO
    ---------- --- ------
    01/01/2010 A   DEPTA 
    02/01/2010 A   DEPTA 
    03/01/2010 A   DEPTA 
    04/01/2010 A   DEPTA 
    01/05/2010 A   DEPTB 
    02/05/2010 A   DEPTB 
    03/05/2010 A   DEPTB 
    04/05/2010 A   DEPTB 
    05/05/2010 A   DEPTB 
    06/05/2010 A   DEPTB 
    07/05/2010 A   DEPTB 
    08/05/2010 A   DEPTB 
    01/01/2010 B   DEPTA 
    02/01/2010 B   DEPTA 
    03/01/2010 B   DEPTA 
    04/01/2010 B   DEPTA 
    05/01/2010 B   DEPTA 
    06/01/2010 B   DEPTA 
    01/07/2010 B   DEPTB 
    02/07/2010 B   DEPTB 
    03/07/2010 B   DEPTB 
    04/07/2010 B   DEPTB 
    05/07/2010 B   DEPTB 
    06/07/2010 B   DEPTB 
    
    24 rows selected.
  • 6. Re: How to use CONNECT BY LEVEL to get this result?
    odie_63 Guru
    Currently Being Moderated
    Or, similar solution, using MULTISET operator :
    SQL> with t  as (
      2   select date '2010-01-01' from_dt,date '2010-04-30' to_dt,'A' eno,'DEPTA' deptno from dual union
      3   select date '2010-05-01',date '2010-12-31','A','DEPTB' from dual union
      4   select date '2010-01-01',date '2010-06-30','B','DEPTA' from dual union
      5   select date '2010-07-01',date '2010-12-31','B','DEPTB' from dual
      6  )
      7  select to_char(m.column_value, 'YYYY-MM') as mon, eno, deptno
      8  from t,
      9       table(
     10         cast(
     11           multiset(
     12             select add_months(trunc(from_dt, 'MM'), level - 1)
     13             from dual
     14             connect by level <= round(months_between(to_dt, from_dt))
     15           )
     16           as sys.odcidatelist
     17         )
     18       ) m
     19  ;
     
    MON     ENO DEPTNO
    ------- --- ------
    2010-01 A   DEPTA
    2010-02 A   DEPTA
    2010-03 A   DEPTA
    2010-04 A   DEPTA
    2010-01 B   DEPTA
    2010-02 B   DEPTA
    2010-03 B   DEPTA
    2010-04 B   DEPTA
    2010-05 B   DEPTA
    2010-06 B   DEPTA
    2010-05 A   DEPTB
    2010-06 A   DEPTB
    2010-07 A   DEPTB
    2010-08 A   DEPTB
    2010-09 A   DEPTB
    2010-10 A   DEPTB
    2010-11 A   DEPTB
    2010-12 A   DEPTB
    2010-07 B   DEPTB
    2010-08 B   DEPTB
    2010-09 B   DEPTB
    2010-10 B   DEPTB
    2010-11 B   DEPTB
    2010-12 B   DEPTB
     
    24 rows selected
     
  • 7. Re: How to use CONNECT BY LEVEL to get this result?
    William Robertson Oracle ACE
    Currently Being Moderated
    Good point, different CONNECT BY expressions may not be equivalent in a join like this.

    You could also explicitly separate the row generation and data queries:
    select add_months(trunc(from_dt, 'MON'), rownum - 1) as months, eno, deptno, t1.bet, seq.rn
    from   ( select from_dt, to_dt, eno, deptno, round(months_between(to_dt, from_dt)) as bet from t ) t1
         , ( select rownum rn from dual connect by rownum <= 600 ) seq
    where  seq.rn <= t1.bet
    order  by eno, deptno, months;
  • 8. Re: How to use CONNECT BY LEVEL to get this result?
    Ganesh Srivatsav Guru
    Currently Being Moderated
    The generation of level should be done for every row seperately. and then join with the result.
    SQL> select add_months (from_dt, levl - 1) month, eno, deptno
      2    from t,(  select level levl
      3                from (select max (ceil (months_between (to_dt, from_dt))) mx from t)
      4          connect by level <= mx) mnths
      5   where levl <= ceil (months_between (to_dt, from_dt)) order by eno,deptno,month;
    
    MONTH     EN DEPTNO
    --------- -- ----------
    01-JAN-10 A  DEPTA
    01-FEB-10 A  DEPTA
    01-MAR-10 A  DEPTA
    01-APR-10 A  DEPTA
    01-MAY-10 A  DEPTB
    01-JUN-10 A  DEPTB
    01-JUL-10 A  DEPTB
    01-AUG-10 A  DEPTB
    01-SEP-10 A  DEPTB
    01-OCT-10 A  DEPTB
    01-NOV-10 A  DEPTB
    01-DEC-10 A  DEPTB
    01-JAN-10 B  DEPTA
    01-FEB-10 B  DEPTA
    01-MAR-10 B  DEPTA
    01-APR-10 B  DEPTA
    01-MAY-10 B  DEPTA
    01-JUN-10 B  DEPTA
    01-JUL-10 B  DEPTB
    01-AUG-10 B  DEPTB
    01-SEP-10 B  DEPTB
    01-OCT-10 B  DEPTB
    01-NOV-10 B  DEPTB
    01-DEC-10 B  DEPTB
    
    24 rows selected.
    
    SQL> 
    G.
  • 9. Re: How to use CONNECT BY LEVEL to get this result?
    672680 Pro
    Currently Being Moderated
    Thank every one of you VERY MUCH.

    Never thought this could be done is this many ways :)

    Wish you all have a great day!
  • 10. Re: How to use CONNECT BY LEVEL to get this result?
    672680 Pro
    Currently Being Moderated
    >
    You could also explicitly separate the row generation and data queries:

    select add_months(trunc(from_dt, 'MON'), rownum - 1) as months, eno, deptno, t1.bet, seq.rn
    from ( select from_dt, to_dt, eno, deptno, round(months_between(to_dt, from_dt)) as bet from t ) t1
    , ( select rownum rn from dual connect by rownum <= 600 ) seq
    where seq.rn <= t1.bet
    order by eno, deptno, months;
    >

    correction: ROWNUM used in below needs to be replaced with SEQ.RN
    add_months(trunc(from_dt, 'MON'), ROWNUM- 1) as months    ---use SEQ.RN to get the right result
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points