How do I create a field of years

[RycherX]

I want a table with one field.
I want values to be the current year, the year before, and the year before that...etc....
I want this table to be updated on the fly.

I have the following, but these are in separate fields which I don't want happen.

select to_char(sysdate, 'YYYY'), to_char(sysdate - 365.25, 'YYYY'),to_char(sysdate - (365.25)*2, 'YYYY'), to_char(sysdate - (365.25)*3, 'YYYY'),to_char(sysdate - (365.25)*4, 'YYYY') from dual

 

[SantaMufasa]

Rycher,

We can certainly suggest ways to make that happen for you, but first, I have a some questions:

1) How far back do you want to go (i.e, how many "past years" do you want in the table)?

2) What is the business/processing purpose of a table that simply lists/contains "years"?

3) By what method will you be joining to the "years" table?

If you can provide us insight on answers to these questions, I'm sure we can help you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RycherX]

I want to go about 5 years back.
Purpose is to feed a drop down list box on a web page.
I'll be Frontpage tool to connect to the Oracle DB Table or View.

 

[SantaMufasa]

Rycher,

Now that I understand better what you want, I believe a simple view should resolve your need without actually taking up any space in the database:

create or replace view years as
select to_char(sysdate,'yyyy')-(rownum-1) yr
from all_objects
where rownum <= 5;

View created.

SQL> select * from years;

        YR
----------
      2006
      2005
      2004
      2003
      2002

5 rows selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.