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.
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.