10 Replies Latest reply: Apr 10, 2011 9:01 PM by 672680 RSS

    How to use CONNECT BY LEVEL to get this result?

    672680
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            >
                            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