DECODE function

[mutsy]

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

[chrisrlong]

No - you cannot do directly what you are asking.

However, there are multiple ways around it:

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

OR

DECODE ( TRUNC(patient_age/15),0,'less than 15', '15 or over')

- Chris

[OCPIP]

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.

[jmodic]

So how is this better than Chris's sollution?

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

[jmodic]

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:

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

[OCPIP]

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.

[pando]

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