Re: Oracle SQL: CASE Statement

"David L Jones" <davidljones1978_at_hotmail.com> wrote in message news:52dd301f.0202120845.4b1cf5d4_at_posting.google.com...
>
> However, the datatypes I'm seeking an alternative for are
 non-limited
> data. There isn't a limited number of values that it can be which
> means I cannot use DECODE. What I need an alternative to the CASE
> statement for is:
>
> SELECT CASE
> WHEN someDate BETWEEN '2001-01-01' AND '2002-01-01' THEN
> 'lastYear'
> WHEN someDate BETWEEN '2000-01-01' AND '2001-01-01' THEN
> 'yearBefore'
> ELSE 'unknown'
>
> Also, I cannot guarantee that the ranges i specify join up. I.e.
 there
> may be gaps between them. Therefore I cannot use SIGN(sysdate -
> mydate), as this could only give a less than/more than option and
 not
> a between.

I must be missing something ... why wouldn't you have a table as follows:

create table date_label

    id
    start_date
    end_date
    val

Then you would write your query as follows:

SELECT a.*, NVL(b.val, 'Unknown')
  FROM my_table_a a,

       date_label b
 WHERE a.someDate BETWEEN b.start_date (+) AND b.end_date (+);

--
Pablo Sanchez, High-Performance Database Engineering
www.hpdbe.com
Available for short-term and long-term contracts