mardi 11 septembre 2018

Case condition in where clause in Oracle

I have a pull some data out of Oracle tables based on some condition.

If a person with JobFunctionCode = 'ADMIN' then when he resign the notified_termination_date populate and then his data should be picked up.

Person from other JobFunctionCode like Manager, Finance etc when they resign the accepted_termination_date populates then their data should be picked up.

So pull out data based on these condition I wrote a case statement in my WHERE clause, but its giving missing parenthesis error. This SQL is a scheduled run so I have set the termination dates to sysdate and this will run on a daily basis.

select ppos.person_id, paam.position_id, pjf.job_function_code 
from 
    per_all_assignments_m paam, per_periods_of_service ppos, per_jobs_f pjf
    where
    case
        when    pjf.job_function_code = 'ADMIN' then (TO_CHAR(ppos.notified_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD'))
        else    (TO_CHAR(ppos.accepted_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD'))
    end
    and paam.person_id = ppos.person_id
    and paam.job_id = pjf.job_id
    and paam.primary_flag = 'Y'
    and TO_CHAR(paam.effective_end_date, 'YYYY-MM-DD') = '4712-12-31'
    and TO_CHAR(pjf.effective_end_date, 'YYYY-MM-DD') = '4712-12-31'

Also there can be condition when Person from both JobFunctionCode terminates then both person data should be pulled out, so I don't think in this case 'CASE' statement can be used, Please let me know how to write the logic here ?

Thanks,

Shivam

Aucun commentaire:

Enregistrer un commentaire