SQL & PL/SQL

If you are on 10g or higher:

SQL> create table mytable
  2  as
  3  select date '2007-10-01' start_date, null end_date from dual union all
  4  select date '2007-10-02', date '2007-10-05' from dual union all
  5  select date '2007-10-02', null from dual union all
  6  select date '2007-10-03', null from dual union all
  7  select date '2007-10-03', null from dual union all
  8  select date '2007-10-20', date '2007-10-21' from dual
  9  /

Tabel is aangemaakt.

SQL> select distinct start_date
  2    from mytable
  3   model
  4         partition by (rownum r)
  5         dimension by (0 i)
  6         measures (start_date, end_date)
  7         rules
  8         ( start_date[for i from 1 to end_date[0] - start_date[0] increment 1] = start_date[0] + cv(i)
  9         )
 10   order by start_date
 11  /

START_DATE
-------------------
01-10-2007 00:00:00
02-10-2007 00:00:00
03-10-2007 00:00:00
04-10-2007 00:00:00
05-10-2007 00:00:00
20-10-2007 00:00:00
21-10-2007 00:00:00

7 rijen zijn geselecteerd.

Regards,
Rob.

This may helps

SELECT COL1 FROM (
SELECT '01-OCT-2007' COL1, NULL COL2 FROM DUAL
UNION
SELECT '02-OCT-2007', '05-OCT-2007' FROM DUAL
UNION
SELECT '02-OCT-2007', NULL FROM DUAL
UNION
SELECT '03-OCT-2007', NULL FROM DUAL
UNION
SELECT '03-OCT-2007', NULL FROM DUAL
UNION
SELECT '20-OCT-2007', '21-OCT-2007' FROM DUAL )
UNION
SELECT COL2 FROM (
SELECT '01-OCT-2007' COL1, NULL COL2 FROM DUAL
UNION
SELECT '02-OCT-2007', '05-OCT-2007' FROM DUAL
UNION
SELECT '02-OCT-2007', NULL FROM DUAL
UNION
SELECT '03-OCT-2007', NULL FROM DUAL
UNION
SELECT '03-OCT-2007', NULL FROM DUAL
UNION
SELECT '20-OCT-2007', '21-OCT-2007' FROM DUAL )

COL1
-----------
01-OCT-2007
02-OCT-2007
03-OCT-2007
05-OCT-2007
20-OCT-2007
21-OCT-2007
[pre]

HTH
Ghulam                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Thanks Ghulam, unfortunately the query does not return 04-OCT-2007 which lies within the range.

Hi Rob,

Thanks for the awesome query! Any chance this can be done in v9.2?

SQL> with tbl as
  2  (select 1 st, 3 ed from dual union all
  3   select 2 st, null from dual union all
  4   select 5 st, null from dual union all
  5   select 8 st, 12 ed from dual)
  6  select distinct st+level-1
  7  from   tbl
  8  connect by level <= ed-st+1
  9  order by st+level-1;

ST+LEVEL-1
----------
         1
         2
         3
         5
         8
         9
        10
        11
        12

9 rows selected.

Nicolas.

Too late.
Message was edited by:
N. Gasparotto

Thanks alot! Just what I needed.

There is this line that confuses me:

prior dbms_random.value is not null

Please enlighten me as to what the purpose for this is. I have tried to take this out but returned with a

ORA-01436: CONNECT BY loop in user data

. I've also tried to replace dbms_random.value with a constant (i.e. the number 1) and generates the same error as well. From this, it's obviously used as a condition to stop an infinite loop...but how?

Doesn't dbms_random.value always return a not null?

As you have found out, it's a trick to prevent the ORA-01436 indeed.

I learned it from Volder, who explained it [url http://forums.oracle.com/forums/message.jspa?messageID=1855638#1855638]here.

Regards,
Rob.

create table dayTable(StartDay,EndDay) as
select to_date('2007/10/01','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/02','yyyy/mm/dd'),to_date('2007/10/05','yyyy/mm/dd') from dual union all
select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/03','yyyy/mm/dd'),to_date(null) from dual union all
select to_date('2007/10/20','yyyy/mm/dd'),to_date('2007/10/21','yyyy/mm/dd') from dual union all
select to_date('2007/12/31','yyyy/mm/dd'),to_date('2007/12/31','yyyy/mm/dd') from dual;

create or replace type OutputDayType as table of date;
/

create or replace function PrintOutputDayType return OutputDayType PipeLined IS
begin
    for rec in (select StartDay,EndDay
                  from dayTable
                 order by StartDay) Loop

        if rec.EndDay is null then
            pipe row(rec.StartDay);
        else
            for i in 0..rec.EndDay-rec.StartDay Loop
                pipe row(rec.StartDay+i);
            end Loop;
        end if;
    end loop;
end;
/

sho err

create or replace view PrintOutputView(day1) as
select distinct *
  from table(PrintOutputDayType)
with read only;

select day1
  from PrintOutputView
order by day1;

DAY1
--------
07-10-01
07-10-02
07-10-03
07-10-04
07-10-05
07-10-20
07-10-21
07-12-31