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