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.

The 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 conditions 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 the condition when Person from both JobFunctionCode terminates then both person data should be pulled out, so I don't think in this case a CASE expression can be used.

Please let me know how to write the logic here?

2

3 Answers

The where clause should be:

where ( (pjf.job_function_code = 'ADMIN' and TO_CHAR(ppos.notified_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) or (pjf.job_function_code <> 'ADMIN' and TO_CHAR(ppos.accepted_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) ) and <rest of clauses>

I havent looked at the rest of it, whether the TO-CHAR is required, etc, but this will fix your where clause.

You can try :

((case when pjf.job_function_code = 'ADMIN' case when (TO_CHAR(ppos.notified_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) then 'Y' else 'N' end else case when (TO_CHAR(ppos.accepted_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) then 'Y' else 'N' end end )='Y' or exists (select * from <<tablename>> pjf.job_function_code = 'ADMIN' and (TO_CHAR(ppos.notified_termination_date, 'YYYY-MM-DD')) = (TO_CHAR(ppos.accepted_termination_date, 'YYYY-MM-DD'))));

P.S: Just check the brackets closure, and write the query in exists clause as per your table design

In where clause case when is not allowed, you can write your condition below way

where ( pjf.job_function_code = 'ADMIN' AND (TO_CHAR(ppos.notified_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) Or (TO_CHAR(ppos.accepted_termination_date, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')) ) 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'

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobGtqaG19d3yOnJisnV2axbG%2BxKyqoqeeYravedahnKudXZi5osHSnmSipl2kv6Kvy54%3D