Sql查询，根据特定的列条件查找职位和级别

Question

我创建了一个查询-

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

现在我想要这样的输出，根据legal_emp和SAL_hour的值，我将不得不从同一查询中的另一个表中计算等级和工作。

类似

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')

如何在一个查询中实现这一点？

Answer 1

听起来像CASE WHEN应该能够为您解决这个问题：

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)