DECODE

I have a column in a table that can contain the value between 25 and 60, 61 and 86, 87 and 112 etc.

How would I write a DECODE function to return a 1, 2 and 3 depending on the value of this column?

25 -> 60, return 1
61 -> 86, return 2
87 -> 112, return 3

Thanks in advance, Keith

select 1 from table_name where column_name between 25 and 60
union
select 2 from table_name where column_name between 61 and 86
union
select 3 from table_name where column_name between 87 and 112

If you are using 8.1.6.x - Don't! Use a CASE statement instead:

SELECT
---CASE
------WHEN Col1 BETWEEN 25 AND 60 THEN 1
------WHEN Col1 BETWEEN 61 AND 86 THEN 2
------WHEN Col1 BETWEEN 87 AND 112 THEN 3
---END
FROM
---TABLE1

- Chris

Follow Chris's advice, use CASE if you are on 8.1.6 or higher. If you are not (and I'm also not sure if CASE is already supported inside PL/SQL) then you can use the following:

SELECT
--DECODE(1,
----SIGN(SIGN(col1-25)+SIGN(60-col1)), 1,
----SIGN(SIGN(col1-61)+SIGN(86-col1)), 2,
----SIGN(SIGN(col1-87)+SIGN(112-col1)), 3)
FROM
--table1;

As I've mentioned in another thread, hardly anyone could tell what this construct does by looking at it for just a couple of seconds. On the other hand, if you look at the CASE construct for just one second it is more than clear what is the intention of the code. So, if you can choose between DECODE and CASE it should not really be a very tough decision.

HTH,