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

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

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.

So how is this better than Chris's sollution?

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.

Quote:

---

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...

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.

Quote:

---

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