代码之家 › 专栏 › 技术社区 › bobby

使用有效日期记录

peoplesoft oracle

0

bobby · 技术社区 · 17 年前

如何获取员工,例如,在生效日期记录中的5个最新操作原因行,没有未来行,应该只选择当前行和历史行(生效日期<=sysdate)。我能用一行来取这些,还是给员工5行呢?

select emplid, effdt, action_reasons
-- we have to build a logic here.
-- Should we initialize 5 ACT variables to fetch rows into it?
-- Please help
from JOB
where emplid = '12345'
  and effdt <= sysdate.

3 回复 | 直到 17 年前

1

3

Quassnoi 17 年前

SELECT  LTRIM(SYS_CONNECT_BY_PATH(emplid || ', ' || effdt || ', ' || action_reasons, ', '), ', ')
FROM    (
        SELECT 
        FROM (
             SELECT emplid, effdt, action_reasons, ROW_NUMBER() OVER (ORDER BY effdt) AS rn
             FROM   JOB
             WHERE  emplid= '12345'
              AND  effdt <= SYSDATE
             )
        WHERE rn <= 5
        )
WHERE   CONNECT_BY_ISLEAF = 1
START WITH  
    rn = 1
CONNECT BY
    rn = PRIOR rn + 1

2

0

Quassnoi 17 年前

SELECT JOBXX.EMPLID,JOBXX.EFFDT,JOBXX.ACT1,JOBXX.ACT2,JOBXX.ACT3,JOBXX.ACT4,JOBXX.ACT5
FROM
  (SELECT SD.EMPLID,
          SD.EFFDT,
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 1 THEN SD.A1 ELSE 0 END)),1,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 1 THEN SD.A1 ELSE 0 END)),3,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 1 THEN SD.A1 ELSE 0 END)),5,2))
          AS ACT1,
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 2 THEN SD.A1 ELSE 0 END)),1,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 2 THEN SD.A1 ELSE 0 END)),3,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 2 THEN SD.A1 ELSE 0 END)),5,2))
          AS ACT2,
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 3 THEN SD.A1 ELSE 0 END)),1,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 3 THEN SD.A1 ELSE 0 END)),3,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 3 THEN SD.A1 ELSE 0 END)),5,2))
          AS ACT3,
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 4 THEN SD.A1 ELSE 0 END)),1,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 4 THEN SD.A1 ELSE 0 END)),3,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 4 THEN SD.A1 ELSE 0 END)),5,2))
          AS ACT4,
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 5 THEN SD.A1 ELSE 0 END)),1,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 5 THEN SD.A1 ELSE 0 END)),3,2)) ||
          CHR(SUBSTR(TO_CHAR(SUM(CASE WHEN SD.R3 = 5 THEN SD.A1 ELSE 0 END)),5,2))
          AS ACT5
  FROM (
         SELECT EMPLID,EFFDT,ACTION_REASON,
                SUBSTR(ACTION_REASON,1,1),
                SUBSTR(ACTION_REASON,2,1),
                SUBSTR(ACTION_REASON,3,1),
                TO_NUMBER(ASCII(SUBSTR(ACTION_REASON,1,1)) ||
                ASCII(SUBSTR(ACTION_REASON,2,1)) ||
                ASCII(SUBSTR(ACTION_REASON,3,1))) AS A1,
                ROW_NUMBER() over(PARTITION BY EMPLID,EFFDT ORDER BY EFFDT desc,EFFSEQ desC) R3
        FROM PS_JOB
        WHERE action in ('ABC','XYZ')
        and action_reason in ('123','456','789')
        and emplid IN('12345','ABCDE')
        AND effdt  between '01-jan-2008' and '18-dec-2008'
        ORDER BY EFFDT DESC, EFFSEQ DESC
    ) SD                        
  GROUP BY EMPLID , EFFDT              
) JOBXX

3

0

PeopleSoftTipster 17 年前

你可以按你希望的方式得到数据。如果您希望它是五行,那么您可以使用这个:

select * from (
             select emplid, empl_rcd, effdt, action_reason
                  , rank() over (partition by emplid, empl_rcd 
                                 order by effdt desc, effseq desc) rank1
               from ps_job
              where emplid = '12345'
                and effdt <= sysdate)
 where rank1 <= 5

如果希望所有数据都在一行上,请使用Oracle的滞后分析函数,从而:

select * from ( 
    select emplid, empl_rcd, effdt
         , lag(effdt) over(partition by emplid, empl_rcd order by effdt, effseq) effdt_lag1
         , lag(effdt, 2) over(partition by emplid, empl_rcd order by effdt, effseq) effdt_lag2
         , lag(effdt, 3) over(partition by emplid, empl_rcd order by effdt, effseq) effdt_lag3
         , lag(effdt, 4) over(partition by emplid, empl_rcd order by effdt, effseq) effdt_lag4
         , action_reason
         , lag(action_reason) over(partition by emplid, empl_rcd order by effdt, effseq) action_reason_lag1
         , lag(action_reason, 2) over(partition by emplid, empl_rcd order by effdt, effseq) action_reason_lag2
         , lag(action_reason, 3) over(partition by emplid, empl_rcd order by effdt, effseq) action_reason_lag3
         , lag(action_reason, 4) over(partition by emplid, empl_rcd order by effdt, effseq) action_reason_lag4
      from ps_job
     where emplid = '12345') j
 where effdt = (
            select max(j1.effdt) from ps_job j1
             where j1.emplid = j.emplid
               and j1.empl_rcd = j.empl_rcd
               and j1.effdt <= sysdate)

这将给出最后5个effdt值和最后5个动作原因值。如果您不需要两者,上述SQL可以相应地进行裁剪。