RE: sql question
- From: "Stauffer, Robert G" <rstauffer@xxxxxxxxxxxxxxxxxxxx>
- To: <tim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 17:03:57 -0400
Or maybe something like:
SQL> select rownum+&begin-1 from big_table where rownum <
((&end+1)-(&begin-1));
Enter value for begin: 1995
Enter value for end: 2006
Enter value for begin: 1995
old 1: select rownum+&begin-1 from watchdog.maintenance_log where
rownum < ((&end+1)-(&begin-1))
new 1: select rownum+1995-1 from watchdog.maintenance_log where rownum
< ((2006+1)-(1995-1))
ROWNUM+1995-1
-------------
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
12 rows selected.
SQL>
Bob Stauffer
DBA
D&E Communications
Ephrata, PA, USA
717-738-8737
rstauffer@xxxxxxxxxxxxxxxxxxxx
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Thursday, April 20, 2006 16:54
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: sql question
Chris,
Hee hee! I get to trot out the good ol' BETWIXT pipelined table
function, which you can use in 8i upwards. Source code is downloadable
from "http://www.EvDBT.com/betwixt.sql";
<http://www.EvDBT.com/betwixt.sql> ...
SQL> select * from table(betwixt('15-APR-2006','20-APR-2006'));
DT
-----------
16-APR-2006
17-APR-2006
18-APR-2006
19-APR-2006
20-APR-2006
5 rows selected.
SQL> select to_char(dt,'YYYY') from
table(betwixt('01-JAN-1995','01-JAN-2006',366));
TO_C
----
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
11 rows selected.
Hope this helps!
-Tim
Chris Stephens wrote:
Can anyone think of a way to generate a list of years from say
1995 to
the current year in straight sql?
chris
--
//www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use
of the individual or entity to which they are addressed and may contain
information that is privileged, proprietary and confidential. If you are not
the intended recipient, you may not use, copy or disclose to anyone the message
or any information contained in the message. If you have received this
communication in error, please notify the sender and delete this e-mail
message. The contents do not represent the opinion of D&E except to the extent
that it relates to their official business.
Other related posts:
