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,
...