Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How Level Cause Works?

FlakeNov 7 2009 — edited Nov 9 2009
I tried to figure out how "connect by level" clause works, but I am not able to come up with how it works.
As a try-out, I created a table with 3 column "from_date" date type, "to_date" date type and "user_name" varchar type.
It has 2 rows:
FROM_DATE   TO_DATE     USER_NAME
------------------   --------------   ------------------
15-OCT-09     18-OCT-09   thomas
25-OCT-09     30-OCT-09   jane
Upon executing the following SQL: 
SELECT DISTINCT level - 1 + from_date "DAY", user_name
FROM my_table
CONNECT BY LEVEL < (to_date - from_date + 1)
ORDER BY user_name;
I get the following results:
 LEVEL      DAY            USER_NAME
-----------   ----------------   ------------------
         1    25-OCT-09   jane
         2    26-OCT-09   jane
         3    27-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         1   15-OCT-09   thomas
         2   16-OCT-09   thomas
         3   17-OCT-09   thomas

[This is an exact presentation that I am looking for]
But, if DISTINCT is ommitted, query returns many rows as follows: 
LEVEL      DAY            USER_NAME
-----------   ----------------  -----------------
         5    29-OCT-09   jane
         4    28-OCT-09   jane
         3    27-OCT-09   jane
         5    29-OCT-09   jane
         4    28-OCT-09   jane
         2    26-OCT-09   jane
         5    29-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         3    27-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         2    26-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         3    27-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         1    25-OCT-09   jane
         4    28-OCT-09   jane
         5    29-OCT-09   jane
         3    27-OCT-09   jane
         4    28-OCT-09   jane
         2    16-OCT-09   thomas
         3    17-OCT-09   thomas
         3    17-OCT-09   thomas
         3    17-OCT-09   thomas
         3    17-OCT-09   thomas
         2    16-OCT-09   thomas
         1    15-OCT-09   thomas
So, how do I eliminate duplicates in "level" column within same "user_name" without using DISTINCT (as it requires sorting). And my production db has many many rows the table that I have to work on and my application invokes this query many times. So, its unimaginable to think of resource usage by way of using DISTINCT. Does any expert have a workaround for this? I'd like to eliminate GROUP BY, DISTINCT clause and use any alternative light-weight clause instead.
Help is highly appreciated.

Regards,
...

Comments

Etbin
NOT TESTED! http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#sthref790 
SELECT level - 1 + from_date "DAY", user_name
  FROM my_table
 WHERE connect_by_isleaf = 1
CONNECT BY LEVEL < (to_date - from_date + 1)
ORDER BY user_name;
Regards

Etbin
Solomon Yakobson 
with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  column_value,
        from_date + column_value - 1 day,
        user_name
  from  my_table,
        table(cast(multiset(select level from dual connect by from_date + level <=to_date) as sys.OdciNumberList))
  order by user_name,
           column_value
/

COLUMN_VALUE DAY       USER_N
------------ --------- ------
           1 25-OCT-09 jane
           2 26-OCT-09 jane
           3 27-OCT-09 jane
           4 28-OCT-09 jane
           5 29-OCT-09 jane
           1 15-OCT-09 thomas
           2 16-OCT-09 thomas
           3 17-OCT-09 thomas

8 rows selected.

SQL>
SY.
Solomon Yakobson
Other solutions:
with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  lvl,
        from_date + lvl - 1 day,
        user_name
  from  my_table,
        (select level lvl from dual connect by level <= (select max(to_date) - min(from_date) from my_table))
  where to_date >= from_date + lvl
  order by user_name,
           lvl
/

       LVL DAY       USER_N
---------- --------- ------
         1 25-OCT-09 jane
         2 26-OCT-09 jane
         3 27-OCT-09 jane
         4 28-OCT-09 jane
         5 29-OCT-09 jane
         1 15-OCT-09 thomas
         2 16-OCT-09 thomas
         3 17-OCT-09 thomas

8 rows selected.

SQL> 
with my_table as (
                  select date '2009-10-15' from_date,date '2009-10-18' to_date,'thomas' user_name from dual union all
                  select date '2009-10-25',date '2009-10-30','jane' from dual
                 )
select  level,
        from_date + level - 1 day,
        user_name
  from  my_table
  connect by user_name = prior user_name
         and from_date + level <=to_date
         and prior dbms_random.random is not null
  order by user_name,
           level
/

     LEVEL DAY       USER_N
---------- --------- ------
         1 25-OCT-09 jane
         2 26-OCT-09 jane
         3 27-OCT-09 jane
         4 28-OCT-09 jane
         5 29-OCT-09 jane
         1 15-OCT-09 thomas
         2 16-OCT-09 thomas
         3 17-OCT-09 thomas

8 rows selected.

SQL>
Although DBMS_RANDOM behavior in hierarchical queries is version dependent.

SY.
Hoek
Wrong, hoek, you should have read up to the part where OP stated:
I'd like to eliminate GROUP BY, DISTINCT clause and use any alternative light-weight clause instead.
Edited by: hoek on Nov 7, 2009 1:48 PM
Solomon Yakobson
hoek wrote:
Hi,

Another way:
And how is it different from DISTINCT? Just by doing GROUP BY for the same purpose?

SY.
Hoek
Whoopsy!

Thanks, Solomon.
I should be drinking coffee during weekends as well, before replying on OTN ;)
666352
Hi Hoek,
I think, you need rest :)

Regards salim.
Aketi Jyuuzou
I like model clause ;-)
with my_table as (
select date '2009-10-15' fromD,date '2009-10-18' toD,'thomas' user_name from dual
union all
select date '2009-10-25',date '2009-10-30','jane' from dual)
select fromD,user_name
  from my_table
 model
partition by(RowNum as rn)
dimension by(1 as soeji)
measures(fromD,toD,user_name)
rules(
fromD[for soeji from 1 to toD[1]-fromD[1] increment 1] = fromD[1]+cv(soeji)-1,
user_name[for soeji from 1 to toD[1]-fromD[1] increment 1] = user_name[1]);

FROMD     USER_N
--------  ------
09-10-15  thomas
09-10-16  thomas
09-10-17  thomas
09-10-25  jane
09-10-26  jane
09-10-27  jane
09-10-28  jane
09-10-29  jane
Aketi Jyuuzou
I like recursive with clause better than model clause B-)

Hahaha. I used postgreSQL8.4 :8}
I do not have Oracle11gR2 yet.
with recursive my_table(fromD,toD,user_name) as (
select date '2009-10-15',date '2009-10-18','thomas'
union all
select date '2009-10-25',date '2009-10-30','jane'),
W(fromD,toD,user_name) as(
select fromD,toD,user_name
  from my_table
union all
select (W.fromD+interval '1day')::date,W.toD,W.user_name
  from W,my_table b
 where W.user_name=b.user_name
   and (W.fromD+interval '1day')::date < W.toD)
select fromD,user_name from W
order by user_name,fromD;

   fromd    | user_name
------------+-----------
 2009-10-25 | jane
 2009-10-26 | jane
 2009-10-27 | jane
 2009-10-28 | jane
 2009-10-29 | jane
 2009-10-15 | thomas
 2009-10-16 | thomas
 2009-10-17 | thomas
Aketi Jyuuzou
OOPS there is more simple one.
LikeWise I used PostgreSQL8.4 :-)
with recursive my_table(fromD,toD,user_name) as (
select date '2009-10-15',date '2009-10-18','thomas'
union all
select date '2009-10-25',date '2009-10-30','jane'),
W(fromD,toD,user_name) as(
select fromD,toD,user_name
  from my_table
union all
select (W.fromD+interval '1day')::date,W.toD,W.user_name
  from W
 where (W.fromD+interval '1day')::date < W.toD)
select fromD,user_name from W
order by user_name,fromD;

   fromd    | user_name
------------+-----------
 2009-10-25 | jane
 2009-10-26 | jane
 2009-10-27 | jane
 2009-10-28 | jane
 2009-10-29 | jane
 2009-10-15 | thomas
 2009-10-16 | thomas
 2009-10-17 | thomas
1 - 10
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 7 2009
Added on Nov 7 2009
10 comments
1,184 views