I have created a query –
select distinct Paam.Effective_start_Date, Paam.Effective_end_date, Paam.effective_latest_change, Paam.Assignment_Number, work_term.assignment_id WT_ID, paam.assignment_id, Paam.effective_sequence, (select hl.meaning from fnd_Common_lookups hl where hl.lookup_code = paam.HOURLY_SALARIED_CODE and hl.lookup_type = 'HOURLY_SALARIED_CODE') SAL_hour, (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.GRADE_ID= paam.grade_id) asg_grade_code, paam.grade_id asg_grade_id, Paam.job_id asg_job_id, Paam.action_code, decode(haou_le.name,'Corporate CA','Corporate', 'Corporation_US','Corporate', 'Corp_NM','Corporate', -- 'Auto_Fed','FED', 'Innovative_Fed', 'FED') legal_emp from per_all_assignments_m Paam, per_all_assignments_m work_term, hr_all_organization_units haou_le where 1=1 and work_term.person_id = Paam.person_id and paam.assignment_type = 'E' AND work_term.assignment_type = 'ET' and paam.assignment_status_type = 'ACTIVE' and work_term.assignment_status_type = 'ACTIVE' and paam.legislation_code = 'CA' and haou_le.organization_id = paam.legal_entity_id
Now I want the output such that ,according to the value of legal_emp and SAL_hour I will have to calculate grade and job from another table in the same query.
Something like –
If legal_emp = 'Corporate' and SAL_hour = 'Hourly' then (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.name = 'STU') and (select distinct pj.job_code from PER_jOBS pj where pj.name = 'ABX') If If legal_emp = 'FED' and SAL_hour = 'Hourly' then (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.name = 'HMX') and (select distinct pj.job_code from PER_jOBS pj where pj.name = 'XYZ')
How can i achieve this in a single query ?
Advertisement
Answer
sounds like CASE WHEN should be able to fix this for you:
select *, case when legal_emp = 'Corporate' and SAL_hour = 'Hourly' then (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.name = 'STU') when legal_emp = 'FED' and SAL_hour = 'Hourly' then (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.name = 'HMX') end as grade case when legal_emp = 'Corporate' and SAL_hour = 'Hourly' then (select distinct pj.job_code from PER_jOBS pj where pj.name = 'ABX') when legal_emp = 'FED' and SAL_hour = 'Hourly' then (select distinct pj.job_code from PER_jOBS pj where pj.name = 'XYZ') end as job from (select distinct Paam.Effective_start_Date, Paam.Effective_end_date, Paam.effective_latest_change, Paam.Assignment_Number, work_term.assignment_id WT_ID, paam.assignment_id, Paam.effective_sequence, (select hl.meaning from fnd_Common_lookups hl where hl.lookup_code = paam.HOURLY_SALARIED_CODE and hl.lookup_type = 'HOURLY_SALARIED_CODE') SAL_hour, (select distinct pgf.GRADE_CODE from PER_GRADES pgf where pgf.GRADE_ID= paam.grade_id) asg_grade_code, paam.grade_id asg_grade_id, Paam.job_id asg_job_id, Paam.action_code, decode(haou_le.name,'Corporate CA','Corporate', 'Corporation_US','Corporate', 'Corp_NM','Corporate', -- 'Auto_Fed','FED', 'Innovative_Fed', 'FED') legal_emp from per_all_assignments_m Paam, per_all_assignments_m work_term, hr_all_organization_units haou_le where 1=1 and work_term.person_id = Paam.person_id and paam.assignment_type = 'E' AND work_term.assignment_type = 'ET' and paam.assignment_status_type = 'ACTIVE' and work_term.assignment_status_type = 'ACTIVE' and paam.legislation_code = 'CA' and haou_le.organization_id = paam.legal_entity_id)