1. Junior Member
Join Date
Oct 2002
Posts
1

DECODE function

Hello,

is there a way to give the DECODE function a range of values as the second parameter instead of just one value?

this is what i am doing right now:

DECODE (patient_age, '1', 'less than 15', '2', 'less than 15')etc....

I would like to do : if age is between 1 and 15 , then "less than 15'

Any help, hints would be appreciated!
Thank you

2. No - you cannot do directly what you are asking.

However, there are multiple ways around it:

Code:
```CASE
WHEN patient_age BETWEEN 1 AND 14 THEN
'less than 15'
ELSE
'15 or over'
END```
OR

Code:
`DECODE ( TRUNC(patient_age/15),0,'less than 15', '15 or over')`
- Chris

3. Junior Member
Join Date
Aug 2001
Location
Manchester, UK
Posts
86
Select Patient_age,
DECODE (sign(15-patient_age),1,'Over 15',-1,'Below 15','15 Years Old')
from table_name;

Sign function returns three values 1, if positive, -1 if negative or 0.

HTH,
Anurag.

4. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
So how is this better than Chris's sollution?

5. Junior Member
Join Date
Aug 2001
Location
Manchester, UK
Posts
86
as Chris said,
No - you cannot do directly what you are asking.

Case can be used only in 9i onwards and in PL/SQL only. Question is for Decode.

Truncate can do the job but what if I want to know if the age is equal to 15.

6. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by OCPIP
Truncate can do the job but what if I want to know if the age is equal to 15.
It can be done by using TRUNC also, no problem:
Code:
`DECODE (TRUNC(patient_age/15), (patient_age-15)+1, '15 Years Old', 0,'less than 15', '15 or over')`
There is allways more than one way to skin the cat...
Last edited by jmodic; 10-28-2002 at 06:45 AM.

7. Junior Member
Join Date
Aug 2001
Location
Manchester, UK
Posts
86
You are a Champ.

Never did this using trunc.

There is allways more than one way to skin the cat...
I will always remember this.

8. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
Originally posted by OCPIP
as Chris said,
No - you cannot do directly what you are asking.

Case can be used only in 9i onwards and in PL/SQL only. Question is for Decode.

Truncate can do the job but what if I want to know if the age is equal to 15.

case can be used since 8i, in SQL actually, in PL/SQL you have to put them in dynamic SQL

in 9i when you case in PL/SQL then itīs more like case in other languages, you can use case as well in SQL

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

×