Home » SQL & PL/SQL » SQL & PL/SQL » Calculating age
Calculating age [message #250764] Wed, 11 July 2007 04:19 Go to next message
joeyan
Messages: 4
Registered: 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

I appreciate your help.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no data column in your table. Razz

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 Go to previous messageGo to next message
joeyan
Messages: 4
Registered: 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 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: 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 Go to previous messageGo to next message
joeyan
Messages: 4
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: 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 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: 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 Go to previous messageGo to next message
joeyan
Messages: 4
Registered: July 2007
Location: Hong Kong
Junior Member

thank you all. Razz
Re: Calculating age [message #250843 is a reply to message #250764] Wed, 11 July 2007 09:34 Go to previous message
Bill B
Messages: 1484
Registered: 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: using chr(10) properly
Goto Forum:
  


Current Time: Thu Dec 08 18:10:17 CST 2016

Total time taken to generate the page: 0.14453 seconds