RE: query requirement

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 18 May 2005 10:24:04 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87D91@irvmbxw02>

I have used Jonathan Gennick's method with great success. Another interesting substitute for "pivot" tables on Tom Kyte's website (asktom.oracle.com)
Period Query
http://asktom.oracle.com/pls/ask/f?p=4950:8:8515227363337669542::NO::F49 50_P8_DISPLAYID,F4950_P8_CRITERIA:34808326503816 Or
http://tinyurl.com/8h573
E.g.
Generate 7000 rows

variable rowcount number
execute :rowcount := 7000
select rn
 from
(select rownum as rn

     from dual
     connect by 1 = 1
             and level <= :rowcount

   ) ;  

all dates between A and B  

variable start_date varchar2 (10)
variable end_date varchar2 (10)
execute :start_date := '2005/02/26' ; :end_date := '2005/03/03' select the_date
 from
(select to_date (:start_date, 'YYYY/MM/DD') + rownum - 1 as the_date

     from dual
     connect by 1 = 1
             and level <= to_date (:end_date, 'YYYY/MM/DD') - to_date

(:start_date, 'YYYY/MM/DD') + 1

   ) ;

-----Original Message-----
Jared Still

In addtion to Tim's excellent suggestion, you might like to look at Jonathan Gennick's article on this subject.

http://www.oracle.com/technology/oramag/oracle/02-sep/o52sql.html <http://www.oracle.com/technology/oramag/oracle/02-sep/o52sql.html>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 18 2005 - 13:28:46 CDT