Malvon Malvon - 3 months ago 10
SQL Question

Take Correlated Sub-query Out of CASE WHEN Statement

I have an

employee
table in Oracle that can have either 1 or 2 "future" jobs in table
future_jobs
, a business rule of some sort, e.g.

| employee_id | job_id | job_start_date | job_end_date |
|-------------|--------|----------------|--------------|
| 1 | 127589 | 12-SEP-2016 | 25-DEC-2016 |
| 1 | 834780 | 26-DEC-2016 | 08-AUG-2017 |
| 2 | 800253 | 20-OCT-2016 | 13-APR-2017 |


I have to get the description of each future job by invoking a stored procedure with a specific parameter, e.g.
F1
and
F2
, based on a descending order of
job_start_date
. In example above, for
employee_id = 1
, when the query below is executed for
job_id = 127589
row, since
job_start_date = 12-SEP-2016
is the earliest date among the two rows for
employee_id = 1
,
get_description(emp.employee_id, 'F1')
should be called, and
get_description(emp.employee_id, 'F2')
for
job_id = 834780
.

Currently, I can pull the relevant information with the following query:

select
emp.employee_id,
case
when fj.job_start_date = (select max(job_start_date)
from future_jobs
where employee_id = fj.employee_id
group by employee_id
having count(employee_id) > 1)
then get_description(emp.employee_id, 'F2')
else get_description(emp.employee_id, 'F1')
end job_description,
jd.some_additional_columns
from employees emp
join future_jobs fj
on emp.employee_id = fj.employee_id
join job_details jd
on jd.job_id = fj.job_id
and jd.job_start_date = fj.job_start_date
and jd.job_end_date = fj.job_end_date


However, I would like to know if there is another way to take the correlated sub-query out of CASE WHEN statement? Is there even a way to do it without using the correlated sub-query? I need to have this done in a single statement rather than using
WITH
clause type solution.

Answer

I think you just want window functions:

select emp.employee_id,
       (case when fj.seqnum = 1
             then get_description(emp.employee_id, 'F1')
             else get_description(emp.employee_id, 'F2')
        end) as job_description,
       jd.some_additional_columns
from employees emp join
     (select fj.*,
             row_number() over (partition by employee_id order by fj.job_start_date) as seqnum
      from future_jobs fj
     ) fj
    on emp.employee_id = fj.employee_id join
    job_details jd
    on jd.job_id = fj.job_id and
       jd.job_start_date = fj.job_start_date and
       jd.job_end_date = fj.job_end_date;

I'm not 100% sure the logic is exactly correct. It follows your description and uses F1 for the first future job.