Re: sql question

On 21/04/06, Mladen Gogala <gogala_at_sbcglobal.net> wrote:
> select '1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006' from dual;
>

Far too simple.

I'd create a table (someyears) with a single varchar2 column (theyear) then create a PL/SQL block to generate a range of years which encapsulates the desired range of years. To extract the list of years just use a simple select defining the desired range in the where clause (maybe use between).

I did once legitimately have to do something similar for a project some years ago (it was actually a homework assignment for an MSc one of our project managers was doing in computer science, his idea of doing his homework was to delegate it to someone at work). The system was based around years that didn't match up with calendar years (like academic years or tax years), were not contiguous and were not necessarily the same length as each other (or a calendar year) . For any given calendar date we needed to be able to find which year it fell in.

My solution was to create a three column table with the start and end dates of each year, create a procedure to perform entry of the data (I did it that way, rather than straight inserts, so I could check that there were no overlaps) then create a function to do the lookups and return the year (or null if the requested date fell in a gap).

There's probably a better way to do it but I just wanted something quick and dirty to get it out of the way so I could get on with my day job.

Stephen

--
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/
--
http://www.freelists.org/webpage/oracle-l