Oracle SQL: CASE Statement

Hi,

I know, I know, it's an old question ... read the archives. Sorry.

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 guess what I could do is (using different datatype to show another requirement):

SELECT 'AtoC', compId FROM Companies WHERE compName BETWEEN 'A' AND
'D'

UNION
SELECT 'FtoH', compId FROM Companies WHERE compName BETWEEN 'F' AND
'H'

However, if the user configuring the SQL made a mistake then the same company record could be selected multiple times (which can't happen with DECODE/CASE).

If anyone can offer me a solution to what I'm after I'd really appreciate it.

Oracle 7, 8 & 9 - trying to support <all> versions.

Thanks very much. Received on Tue Feb 12 2002 - 17:46:00 CET