Hi everyone!
I’m studding DB2 at my university and I have a question,
I have to write a query to show the empno and current age of each employee in the employee table.
I did this using

SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE
FROM DANDY.EMPLOYEE
ORDER BY AGE;

Now I have to modify this query and add a third column that shows a count of how many OTHER employees have an age that is less than or equal to that in the current result row. how can i do this?

I'm using DB2 Version 9 on a Windows 7 Enterprise 32bit.

BEGIN ATOMIC
DECLARE AGE2 INT;
SET AGE2= YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE;
SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, COUNT(*) AS OTHER
FROM DANDY.EMPLOYEE
WHEN AGE>AGE2 THEN COUNT(*)
WHEN AGE=AGE2 THEN COUNT(*)
ORDER BY AGE;
END

i tried using a variable(age2) to store the value of AGE but it doesn't work... i'm not sure if i'm thinking right with this query...

i got errors

SET AGE2= YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token ")" was found following "E) - DATE(BIRTHDATE)".
Expected tokens may include: ")". SQLSTATE=42601

SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, COUNT(*) AS OTHER FROM DANDY.EMPLOYEE WHEN AGE>AGE2 THEN COUNT(*) WHEN AGE=AGE2 THEN COUNT(*) ORDER BY AGE
SQL0104N An unexpected token "EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRT"
was found following "SELECT ". Expected tokens may include:
"<case_simple_type>". SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

Thanks for the help,
stefan

What have you tried? As a student, it is best if you learn from your own experience then to rely on someone giving you the answer. Post what you have tried, then ask for advice.

Andy

hi,

i managed to get the following code working

SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, (
SELECT COUNT(*) FROM
(SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) FROM DANDY.EMPLOYEE WHERE YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) < 30))
FROM DANDY.EMPLOYEE;

but instead of 30 i want to put the age and run through the whole table and compare the age...

Firstly, "YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE))" does not make much sense: CURRENT DATE (and, I presume, BIRTHDATE) is already a date, so there's no point in casting it to day. Then, subtracting one date from another gives you a duration, which is a decimal number, so applying YEAR() to it won't return what you expect. In my view, a simpler approach would be to subtract the year portion of the birth date from the year portion of the current date.

You can reference in the subquery columns from the "outer" portion of the query, only you need to assign correlation names to tables so that DB2 understands what you mean, something like:

if you do year(current date) - year(birthdate)... you will not get the correct age

That is true, but that is not what was posted. I suggest that you look closely before you post. What was posted does work.

Andy

Now i understand, why compare the age when you can compare the birthdate... Sorry for not paying attention, and thank you for the help...

Maybe the olap function will help you to do this:

I finished the code and it works perfectly.

SELECT t1.EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE,
(SELECT COUNT(*) - 1 FROM DANDY.EMPLOYEE t2 WHERE YEAR(DATE(CURRENT DATE) - DATE(t2.BIRTHDATE)) <= YEAR(DATE(CURRENT DATE) - DATE(t1.BIRTHDATE))
) AS OTHER
FROM DANDY.EMPLOYEE t1
ORDER BY AGE;

I'm not sure if "YEAR(DATE(CURRENT DATE) - DATE(t1.BIRTHDATE)))" is correct syntax-wise.
Anyway thanks for the help!