larned@uhccux.uhcc.Hawaii.Edu (Paul Larned) (03/05/91)
I am using ORACLE for MS-DOS, ver 5.1b, and sql*plus, ver 2.0. I have been
asked to produce a report which groups individuals by age groups of 5 - 10
year increments. This would seem to be a faily common request. I can
derive age in years from date of birth (DOB) by the following function:
trunc(months_between(sysdate,dob)/12)
However, when I attempt to decode this result to achieve the required
groupings, I cannot seem to write the proper DECODE statement. The
problem appears to be that logical operators are not permitted in the
search argument of the DECODE expression. For example:
decode(trunc(months_between(sysdate,dob)/12),60,'Sixty','Other')
works fine, attaching 'Sixty' ONLY to those records where the function
returns exactly 60, and 'Other' to all others. If, however, I try to
change the search expression [60] to [>60] or [between 60 and 65], I
get an error message stating "missing right parenthesis."
Am I missing some subtle syntax in the DECODE expression, or is it not
possible to do age group collapses in this fashion? If it is not
possible can anyone help me with a strategy to produce these
groupings. I can't believe this problem is *that* uncommon. Needless
to say, I have looked in all the ORACLE manuals and several
after-market books, and can find no help there.
Thanks in advance for any help.
Paul Larned
larned@uhccux.uhcc.hawaii.eduis@athena.cs.uga.edu (Bob Stearns) (03/05/91)
In article <11755@uhccux.uhcc.Hawaii.Edu> larned@uhccux.uhcc.Hawaii.Edu (Paul Larned) writes: >I am using ORACLE for MS-DOS, ver 5.1b, and sql*plus, ver 2.0. I have been >asked to produce a report which groups individuals by age groups of 5 - 10 >year increments. This would seem to be a faily common request. I can >derive age in years from date of birth (DOB) by the following function: > trunc(months_between(sysdate,dob)/12) > However, when I attempt to decode this result to achieve the required > groupings, I cannot seem to write the proper DECODE statement. The > problem appears to be that logical operators are not permitted in the > search argument of the DECODE expression. For example: > decode(trunc(months_between(sysdate,dob)/12),60,'Sixty','Other') > works fine, attaching 'Sixty' ONLY to those records where the function > returns exactly 60, and 'Other' to all others. If, however, I try to > change the search expression [60] to [>60] or [between 60 and 65], I > get an error message stating "missing right parenthesis." > As an old hand at junk like this, why not try something like: decode(trunc(months_between(sysdate,dob)/60,1,'5-9 years','other')) where the 60 in the above is used when you want 5 year ranges and other divisors would be used for other ranges. In particular, in this example the following decode table would be used: 0 = 0-4 years old 1 = 5-9 2 = 10-14 3 = 15-19 and in general, x=5x-5x+4 years of age. Hope this helps.
mwang@.com (Michael Wang) (03/06/91)
In article <11755@uhccux.uhcc.Hawaii.Edu> larned@uhccux.uhcc.Hawaii.Edu (Paul Larned) writes: >I am using ORACLE for MS-DOS, ver 5.1b, and sql*plus, ver 2.0. I have been >asked to produce a report which groups individuals by age groups of 5 - 10 >year increments. This would seem to be a faily common request. I can >derive age in years from date of birth (DOB) by the following function: > trunc(months_between(sysdate,dob)/12) > However, when I attempt to decode this result to achieve the required > groupings, I cannot seem to write the proper DECODE statement. The > problem appears to be that logical operators are not permitted in the > search argument of the DECODE expression. For example: > decode(trunc(months_between(sysdate,dob)/12),60,'Sixty','Other') > works fine, attaching 'Sixty' ONLY to those records where the function > returns exactly 60, and 'Other' to all others. If, however, I try to > change the search expression [60] to [>60] or [between 60 and 65], I > get an error message stating "missing right parenthesis." [...stuff deleted...] Why don't you try something like this: decode(trunc((trunc(months_between(sysdate,birthdate)/12)-1)/10), 0,'1-10',1,'11-20',2,'21-30',3,'31-40',4,'41-50',5,'51-60', 6,'61-70',7,'71-80',8,'81-90',9,'91-100',10,'101-110') Since you are trying to get different age groups, just divide by size of the age group and look at the result without the remainder. You may need to include an offset amount (the -1 in the above example) to handle some groupings. Michael Wang mwang@oracle.com