| How do I achieve range of values using Decode [message #374340] |
Wed, 06 June 2001 11:21  |
Bruce Weinstein
Messages: 4
Registered: May 2001
|
Junior Member |
|
|
Hope someone can help.
Here is my dilemma.
Based on a persons age, I want to return a specific value.
For example, if age < 20 then I want to return string '<20'. If age >=20 and < 25, return '>=20 & <25'.
If age >=25 and < 30, return '>=25 & <30'.
If age >=30 and < 35, return '>=30 & <35'.
And so on...going up by increments of 5 until 65.
I have following DECODE statement
decode(TRUNC(MONTHS_BETWEEN(SYSDATE,BIRTHDATE)/12),<45,'<45')
However, ORACLE does not seem to like it as I keep getting error. My guess is it does not like the <45.
Can someone help me out as I have been searching through the messages in this discussion list with no success.
Thanks in advance.
|
|
|
|
|
|
|
|
| Re: How do I achieve range of values using Decode [message #374349 is a reply to message #374340] |
Thu, 07 June 2001 04:07  |
Sarada
Messages: 27
Registered: April 2001
|
Junior Member |
|
|
Try creating a function and call it from your SELECT statement. Eg:
create function get_age_literal(birthdate date) return varchar2
t_age NUMBER(10);
begin
select trunc(months_between(sysdate, birthdate))/12 INTO t_age from dual;
if t_age < 20 then
return '<20';
elsif t_age between 20 and 25 then
return '>=20 & <25'
.....
end if;
end;
Or you can try a SELECT with UNION clause but it will be cumbersome to do that. Eg:
SELECT emp.*, '<20' "age_literal" from emp
where months_between(sysdate, birthdate)/12 < 20
UNION
SELECT emp.*, '>=20 & <25' "age_literal" from emp
where months_between(sysdate, birthdate)/12 BETWEEN 20 AND 25
UNION
......
so on until all the conditions are ful-filled.
HTH
Orashark
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
