Simple PS/SQL Help on AGE

[TAngel]

I wrote the following statement to trying to take to calculate the employee's age and return a value of N if under 21 else Y but it isn't working. Can someone tell me what I am doing wrong?

In advanced, Thank you
Tee

SELECT
person_ID,
date_of_birth,
case when trunc((sysdate - date_of_birth) / 365) <21 'N' else 'Y' end
FROM per_all_people_f

 

[TAngel]

Please disregard me - I realized after I posted this that I forgot the dreaded "THEN" after my <21

Tee
**who is a first time pl/sql writer**

 

[jimirvine]

can I also suggest a change to your basic age calculation.
Use
MonthsBetween(date1, date2)/12

 

[TAngel]

Thank you. I have been working with db2 on the iSeries writing queries and just learning pl/sql.

 

[SantaMufasa]

TAngel,

Keep in mind that JimIrvine's suggestion is an Oracle SQL function. You can use it in both Oracle SQL statements, and you can use it in PL/SQL, as well.

Your original code is not PL/SQL...it is SQL. As written, it will not work in PL/SQL. Many newbies to the Oracle World confuse the programming environments of PL/SQL and SQL*Plus (probably due to the dyslexic sharing of letters in their names). Those two programming environments do not share code or attributes. (You can run PL/SQL code in a SQL*Plus environment, but you cannot run SQL*Plus commands in PL/SQL code.

SQL*Plus can run command syntax from the following programming environments:[ul][li]SQL*Plus[/li][li]SQL[/li][li]PL/SQL[/li][/ul]SQL can run command syntax from the following programming environments:[ul][li]SQL[/li][li]User-defined functions written in PL/SQL[/li][/ul]PL/SQL can run command syntax from the following programming environments:[ul][li]PL/SQL[/li][li]SQL DML (Data Manipulation Language commands such as SELECT, UPDATE, INSERT, DELETE, ROLLBACK, and COMMIT. SELECT statements require special provisioning in PL/SQL to include either "INTO <variable list>" or SELECT statements must appear in "CURSOR FOR LOOP" constructs, or SELECT statements must appear within normal SQL constructs for INSERT...SELECT or as subqueries in UPDATE or DELETE statements.)[/li][li]SQL DDL (CREATE, ALTER, DROP) or SQL DCL (GRANT or REVOKE) commands must appear within EXECUTE IMMEDIATE statements within PL/SQL[/li][/ul]I know that without training or documentation this is as clear as mud, but I want you to be aware of these three very distinct programming environments within the Oracle World.




Let us know if you have follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[TAngel]

Thank you for the clarification Mufasa. The biggest challenge I am facing is finding the table name to query in the HRMS database. I have been searching for the last hour on what table Oracle stores the employee direct deposit details in. The header is in PAY_PERSONAL_PAYMENT_METHODS_F. Do you know of a good resource on finding table names?

Tee

 

[SantaMufasa]

There are multiple helpful tables:[ul][li]ALL_TABLES -- Lists names (and their owner) and many other helpful details about all tables to which your login has access.[/li][li]ALL_TAB_COLUMS -- Lists names (and their owner) and column names (and many other helpful details) of all tables to which you have access.[/li][/ul]For these (and any other TABLE), you can issue the SQL*Plus command:

DESCRIBE <table_name>

...To see the names and data description of of all columns in <table_name>.


Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[TAngel]

Very helpul! Thank you so very much.
Tee

 

[Thargy]

Tangel,

may I suggest that you try using

FLOOR(MonthsBetween(date1, date2)/12) as in my experience this does the age calculation nicely.



Regards

T