Skip to Main Content

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.

Help With Range Query

543079Oct 23 2007 — edited Nov 8 2007

Hi folks,

Let's say I have a table with this data:

START_DATE  END_DATE
----------- -----------
01-OCT-2007
02-OCT-2007 05-OCT-2007
02-OCT-2007
03-OCT-2007
03-OCT-2007
20-OCT-2007 21-OCT-2007

I would like a query that would give me each unique date including ones that fall between the start and end dates:

01-OCT-2007
02-OCT-2007
03-OCT-2007
04-OCT-2007
05-OCT-2007
20-OCT-2007
21-OCT-2007

Selecting the unique start date is of course not a problem. I am scratching my head on how to grab the list of dates that are within a range.

Please help. Thanks.

Comments

Rob van Wijk

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.

Ghulam Mustafa Butt

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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
543079
Thanks Ghulam, unfortunately the query does not return 04-OCT-2007 which lies within the range.
543079
Hi Rob,

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

> Any chance this can be done in v9.2?

No, because the model clause got introduced in version 10.

In 9.2 you can use something like this:

SQL>  select distinct start_date + level - 1
  2     from (select t.*, rownum rn from mytable t)
  3  connect by level <= nvl(end_date,start_date) - start_date + 1
  4      and prior rn = rn
  5      and prior dbms_random.value is not null
  6    order by 1
  7  /

START_DATE+LEVEL-1
-------------------
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.

Nicolas Gasparotto
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

543079

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?

Rob van Wijk
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.
Aketi Jyuuzou
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
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 6 2007
Added on Oct 23 2007
9 comments
1,857 views