-
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.
-- Anurag.
OCP Application Developer
---------------------------------------------------------
"Be not afraid of growing slowly. Be afraid only of standing still."
-
So how is this better than Chris's sollution?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
-- Anurag.
OCP Application Developer
---------------------------------------------------------
"Be not afraid of growing slowly. Be afraid only of standing still."
-
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 07:45 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
-- Anurag.
OCP Application Developer
---------------------------------------------------------
"Be not afraid of growing slowly. Be afraid only of standing still."
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|