Re: sql question
- From: "stephen booth" <stephenbooth.uk@xxxxxxxxx>
- To: gogala@xxxxxxxxxxxxx
- Date: Fri, 21 Apr 2006 14:02:58 +0100
On 21/04/06, Mladen Gogala <gogala@xxxxxxxxxxxxx> 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/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
