data comparing inside decode function 1

[asunpraise]

The following query doesn't work. It seems decode function doesn't like < or > sign inside of it. But I need to compare clients' birth date to another date to get their age then and add them into a age group. Anybody knows how to do this?

select client_id,
decode(birth_date, MONTHS_BETWEEN(TO_DATE('01022003','DDMMYYYY'), TO_DATE(birth_date, 'DDMMYYYY')) < 13, 'Infant',
MONTHS_BETWEEN(TO_DATE('01022003','DDMMYYYY'), TO_DATE(birth_date, 'DDMMYYYY')) < 24, 'Toddler',
MONTHS_BETWEEN(TO_DATE('01022003','DDMMYYYY'), TO_DATE(birth_date, 'DDMMYYYY')) < 36, '2yr') Age
from clients;

 

[djbjr]

try using a CASE STATEMENT

Like this

select client_id,
CASE WHEN MONTHS_BETWEEN(birth_date, sysdate) < 13
THEN 'Infant'
WHEN MONTHS_BETWEEN(birth_date, sysdate) < 24
THEN 'Toddler'
WHEN MONTHS_BETWEEN(birth_date, sysdate) < 36
THEN '2 year'
END AS AGE
FROM clients;

I think this will work only for 8.1.6 and higher.

Hope this helps

 

[asunpraise]

Exactly!

My version is 8.1.5. and it's doesn't work.

 

[djbjr]

OK well try this

SELECT DECODE(TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('31-DEC-2000') )/12),0 , 'INFANT',1, 'TODDLER', 2, '2 YEAR') FROM DUAL


You might have to tweak it slightly but it will work

 

[asunpraise]

YES!
BRILLIANT!
WISH I COULD HAVE YOUR BRAIN!