Newbie basic SQL question.

I've been asked to extract some data from tables in Oracle for importing in to Lotus Notes, and am struggling over the SQL which is probably very simple (if you know what you are doing).

There's a table that contains an employee's past present [and future] jobs. Each row will have the employee id, job title, and effective date (that they started / will start that job).

All I need to find is the employee's *current* job title.

Whatever I try seems to generate an error, either grouping not allowed in that context, or missing right parenthesis. My latest attempt is:

Select JobTitle from T_Jobs where emplid="12345" and effdt=(Select max(A2.effdt) from T_Jobs A2 where emplid = A2.emplid and A2.effdt <= sysdate)

Where JobTitle is the column I want, emplid is the employee id, and effdt is the date that record is effective from.

Could someone please point me in the right direction.

Thanks - Rufus. Received on Sat Sep 06 2003 - 16:10:06 CEST