从查询中检索单行

斯里

我正在创建一个查询,以查找date_to为'31 -dec-4712'(最新)的员工的薪水详细信息。但是,如果date_to是雇员的两行的31-dec-4712,则在其他情况下,当只有一行进入时,应选择状态为“已批准”的行,则应按原样返回。

我创建了以下查询薪水明细。在上述情况下需要帮助

select distinct PAPF.EMPLOYEE_NUMBER ,
                TO_CHAR (EMP_DOJ (PAPF.PERSON_ID),'DD-MON-YYYY' ) DOJ ,
                TO_CHAR(HR_EMPLOYEE_ORIGINAL_DOJ(PAPF.EMPLOYEE_NUMBER,42) ,'DD-     MON-YYYY' ) ORIGINAL_DOJ,
                PPP.CHANGE_DATE,
                PPP.DATE_TO,
                PPP.PROPOSED_SALARY_N TOTAL_REMUN,
                HR_GENERAL.DECODE_LOOKUP('PER_SAL_PROPOSAL_STATUS',APPROVED) status
from PER_ALL_ASSIGNMENTS_F PAAF,
     PER_ALL_PEOPLE_F PAPF,
     PER_PAY_PROPOSALS PPP
where 1                        = 1
and PAPF.PERSON_ID             = PAAF.PERSON_ID
and PAPF.BUSINESS_GROUP_ID     = 21
and PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
and papf.employee_number       = '109575'
and :P_DATE1 between PAAF.EFFECTIVE_START_DATE
                 and PAAF.EFFECTIVE_END_DATE
and :P_DATE1 between PAPF.EFFECTIVE_START_DATE
                 and PAPF.EFFECTIVE_END_DATE
and :P_DATE1 between PPP.CHANGE_DATE(+)
                 and NVL(PPP.DATE_TO, HR_GENERAL.END_OF_TIME)
and PPP.ASSIGNMENT_ID(+) = PAAF.ASSIGNMENT_ID
order by TO_NUMBER(PAPF.EMPLOYEE_NUMBER);


Emp_num  DOJ             ORIGINAL_DOJ   CHANGE_DATE      DATE_TO      TOTAL_REMUN  STATUS
109575  01-DEC-2016 24-JUL-2014 01-MAY-19   31-DEC-12   250000    Proposed
109575  01-DEC-2016 24-JUL-2014 01-APR-19   31-DEC-12   100000     Approved
思考长臂猿

您可以分别为每个员工使用条件排序,如下所示:

-- sample rows
with salaries (emp_id, name, salary, date_to, status) as (
    select 1001, 'Orange',  1400, date '4712-12-31', 'Rejected' from dual union all
    select 1001, 'Orange',  1200, date '4712-12-31', 'Approved' from dual union all
    select 1002, 'Red',     2500, date '4712-12-31', 'Approved' from dual union all
    select 1003, 'Blue',    2700, date '4712-12-31', 'Proposed' from dual union all
    select 1004, 'Green',   2200, date '2012-07-31', 'Approved' from dual union all
    select 1005, 'White',   1200, date '4712-12-31', 'Approved' from dual union all
    select 1005, 'White',   1300, date '4712-12-31', 'Rejected' from dual )
-- end of sample data

select emp_id, name, salary, date_to, status
  from (
    select s.*, 
           row_number() over (partition by emp_id 
                              order by case status when 'Approved' then 1 end) rn
      from salaries s
      where date_to = date '4712-12-31')
  where rn = 1

结果:

    EMP_ID NAME       SALARY DATE_TO     STATUS
---------- ------ ---------- ----------- --------
      1001 Orange       1200 4712-12-31  Approved
      1002 Red          2500 4712-12-31  Approved
      1003 Blue         2700 4712-12-31  Proposed
      1005 White        1200 4712-12-31  Approved

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章