Home » SQL & PL/SQL » SQL & PL/SQL » Calculating age
Calculating age Wed, 11 July 2007 04:19
 joeyan Messages: 4Registered: July 2007 Location: Hong Kong Junior Member
Hi,

I'm using Oracle 7 and trying to select the information of children (name, dob, sex) who below 21 as at 1.7.2007.

Is it possible to do that?

SELECT CHILDREN.NAME,
CHILDREN.DOB,
CHILDREN.SEX
FROM CHILDREN
WHERE

Thanks a lot.

[Updated on: Wed, 11 July 2007 04:21]

Report message to a moderator

Re: Calculating age [message #250770 is a reply to message #250764] Wed, 11 July 2007 04:37
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
There is no data column in your table.

Have a look at months_between and add_months functions.

Regards
Michel
Re: Calculating age [message #250773 is a reply to message #250770] Wed, 11 July 2007 05:10
 joeyan Messages: 4Registered: July 2007 Location: Hong Kong Junior Member
Thx Michel

I had amended the sql statement.

SELECT DISTINCT
"CHILDREN"."NAME",
"CHILDREN"."DOB",
"CHILDREN"."SEX",
TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) AGE
FROM "CHILDREN"
WHERE AGE > '21';

but it return the error code "ORA-00904 invalid column name' for the column "AGE".

I cannot use it in WHERE clause ?
Re: Calculating age [message #250774 is a reply to message #250764] Wed, 11 July 2007 05:15
 Cthulhu Messages: 381Registered: September 2006 Location: UK Senior Member
"Age" is not a real column. You will either have to use TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12 in the condition in the main query or give it a virtual column name using an inline view.

```select * from
(SELECT DISTINCT
"CHILDREN"."NAME",
"CHILDREN"."DOB",
"CHILDREN"."SEX",
TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) AGE
FROM "CHILDREN")
WHERE AGE > 21;
```
Re: Calculating age [message #250775 is a reply to message #250764] Wed, 11 July 2007 05:21
 joeyan Messages: 4Registered: July 2007 Location: Hong Kong Junior Member
thx Cthulhu.
Re: Calculating age [message #250783 is a reply to message #250773] Wed, 11 July 2007 05:59
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Or directly:
```SELECT DISTINCT
"CHILDREN"."NAME",
"CHILDREN"."DOB",
"CHILDREN"."SEX",
TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) AGE
FROM "CHILDREN"
WHERE TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) > '21';```

Regards
Michel
Re: Calculating age [message #250822 is a reply to message #250764] Wed, 11 July 2007 08:07
 joy_division Messages: 4766Registered: February 2005 Location: East Coast USA Senior Member
 joeyan wrote on Wed, 11 July 2007 05:19 Hi, I'm using Oracle 7 and trying to select the information of children (name, dob, sex) who below 21 as at 1.7.2007.

You probably already knew this, but maybe not...Oracle 7 was desupported as of 31-DEC-2000, almost 7 years ago as of this writing. That's about 3 generations as far as technology is concerned.
Re: Calculating age [message #250834 is a reply to message #250774] Wed, 11 July 2007 08:41
 Bill B Messages: 1662Registered: December 2004 Senior Member
 Cthulhu wrote on Wed, 11 July 2007 05:15 "Age" is not a real column. You will either have to use TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12 in the condition in the main query or give it a virtual column name using an inline view. ```select * from (SELECT DISTINCT "CHILDREN"."NAME", "CHILDREN"."DOB", "CHILDREN"."SEX", TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) AGE FROM "CHILDREN") WHERE AGE > 21; ```

Also, it is not generally a good idea to always use double quotes around your column names and learn to use aliases. Your query should be.

```SELECT DISTINCT C.NAME,C.DOB,c.SEX,TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12) AGE
FROM CHILDREN c
WHERE MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), CHILDREN.DOB) / 12 < 21;
```
Re: Calculating age [message #250841 is a reply to message #250764] Wed, 11 July 2007 09:30
 joeyan Messages: 4Registered: July 2007 Location: Hong Kong Junior Member
thank you all.
Re: Calculating age [message #250843 is a reply to message #250764] Wed, 11 July 2007 09:34
 Bill B Messages: 1662Registered: December 2004 Senior Member
Your welcome, however I just thought of a problem with your query. What happens if the date of birth is after 1/7/2007. USe the following query instead

```SELECT DISTINCT C.NAME,C.DOB,c.SEX,TRUNC(MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), C.DOB) / 12) AGE
FROM CHILDREN c
WHERE MONTHS_BETWEEN(to_date('01/07/2007', 'DD/MM/YYYY'), C.DOB) / 12 < 21
and to_date('01/07/2007', 'DD/MM/YYYY') > c.dob```

[Updated on: Wed, 11 July 2007 09:34]

Report message to a moderator

 Previous Topic: SQL select doubt Next Topic: Refresh the current working Cursor
Goto Forum:

Current Time: Sat Aug 19 21:10:44 CDT 2017

Total time taken to generate the page: 0.29966 seconds