RE: query requirement

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: <jkstill@xxxxxxxxx>, <sjaffarhussain@xxxxxxxxx>
  • Date: Wed, 18 May 2005 10:24:04 -0700

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> 



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

Other related posts: