connect by level

[Patricia Samuel]

I have a table temp_rates

select * from temp_rates

CURRENCY START_DATE END_DATE RATE

AED 25/10/2008 28/10/2008 0.25
AED 08/11/2008 12/11/2008 8
AED 29/10/2008 06/11/2008 0.4

i want to generate multiple rows between end_date and start_date
AED 25/10/2008 0.25
AED 26/10/2008 0.25
AED 27/10/2008 0.25
AED 28/10/2008 0.25

AED 08/11/2008 8
AED 09/11/2008 8
AED 10/11/2008 8
AED 11/11/2008 8
AED 12/11/2008 8

AED 29/10/2008 0.4
AED 30/10/2008 0.4
AED 31/10/2008 0.4
AED 1/11/2008 0.4
AED 2/11/2008 0.4
AED 3/11/2008 0.4
AED 4/11/2008 0.4
AED 5/11/2008 0.4
AED 6/11/2008 0.4


I used 'connect by level' as below

select currency, start_date +level -1 as temp_date, rate from temp_rates
connect by level <= (end_date-start_date +1)

and it returns 930 rows instead of 18. Data is duplicate here.
can anyone tell? what is wrong with this query. i have a doubt on the condition (end_date-start_date +1)

[Patricia Samuel]

Hi ,
Can anyone help on this?

WITH temp AS (
select to_date('01-Jun-2011','DD-Mon-YYYY') start_date, to_date('23-Jun-2011','DD-Mon-YYYY') end_date, 1 rw from dual union all
select to_date('05-Jun-2011','DD-Mon-YYYY'), to_date('07-Jun-2011','DD-Mon-YYYY'), 2 from dual union all
select to_date('05-Jun-2011','DD-Mon-YYYY'), to_date('07-Jun-2011','DD-Mon-YYYY'), 3 from dual
)
SELECT rw
, start_date + t.column_value
, t.column_value "LEVEL"
FROM temp
, TABLE(
CAST(
MULTISET (
SELECT level FROM dual CONNECT BY level <= end_date - start_date
)
AS sys.odcinumberlist
)
) t
ORDER BY 1, 2
;


i found this solution on a forum..
can anyone explain? what is CAST() and MULTISET(), sys.odcinumberlist here