Re: sql question

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Thu, 20 Apr 2006 16:20:49 -0500
Message-ID: <4447FB31.2090502@jcpenney.com>

('binary' encoding is not supported, stored as-is)

or this ;-)

select 1995+n from
 (select level-1 n from dual connect by level <=

        (select to_number(to_char(sysdate,'YYYY'))-1995+1 from dual ) ) /

Stauffer, Robert G wrote:
> 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_at_decommunications.com

>

>

>


> ------------------------------------------------------------------------

> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Tim Gorman
> *Sent:* Thursday, April 20, 2006 16:54
> *To:* oracle-l_at_freelists.org
> *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"...

>


>     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
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>   

> -- http://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.
>
>

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

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

Received on Thu Apr 20 2006 - 16:20:49 CDT