join

[simmat]

Hi,
I am running a query that works as a subquery , but not as a join. Pls help:

This one works:
db2 => SELECT JOB FROM EMPLOYEE WHERE HIREDATE IN (SELECT MIN(HIREDATE) FROM EMP
LOYEE)

JOB
--------
FIELDREP

1 record(s) selected.

This one does not:
select job from employee a , employee b where min(a.hiredate)=b.hiredate
SQL0120N Invalid use of an aggregate functio or OLAP function

Can anyone pls suggest how to convert this subquery to a JOIN statement?

 

[PruSQLer]

Why do you want to make this a join? Obviously the method you tried won't work but the subquery works fine. Is it a performance thing? If so, you could try this...

select job
from employee
order by hiredate
fetch first 1 rows only

 

[Glenn9999]

Can anyone pls suggest how to convert this subquery to a JOIN statement?"

SELECT B.JOB FROM
(SELECT MIN(HIREDATE) AS MIN_HIRE_DATE
FROM EMPLOYEE) A
INNER JOIN
EMPLOYEE B
ON A.MIN_HIRE_DATE = B.HIREDATE;

:S