peoplesoft - 查询管理器 : Trying to bring in the Manager's name

Question

我正在使用 PeopleSoft 的查询管理器来构建一个包含经理姓名的新员工报告。不幸的是，Job - EE 表将报告捕获到位置而不是经理。为了获得经理的名字，我必须再次链接到 Job - EE 表，将这两个表与 A.Reports_To 链接到 G.Position_NBR。所以现在我有 Job - EE 表的两个 View 。问题是我正在查看一个日期范围(2018 年 1 月 1 日至今)，但我无法弄清楚如何在招聘日期的同一时间引入经理。我似乎正在引入在结束生效日期之前一直担任 Reports_To 职位的所有经理。我认为这是因为 Job - EE 表链接到职位(向其报告)而不是实际员工。负责报告的人经常变化。

有人有什么建议吗？
编辑更新 :
以下是各种表、查询管理器标准等的一些图片:




这是 SQL :

    SELECT DISTINCT A.EMPLID, B.NAME, (CONVERT(CHAR(10),A.EFFDT,121)), A.DEPTID, D.DESCR, C.DESCR, A.ANNUAL_RT, E.DESCR, G.EMPLID, G.POSITION_NBR, Concat( H.FIRST_NAME,' ', H.LAST_NAME) 
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1, PS_PERSONAL_DATA B, PS_DEPT_TBL D, PS_SET_CNTRL_REC D2, PS_JOBCODE_TBL C, PS_SET_CNTRL_REC C2, PS_LOCATION_TBL E, PS_SET_CNTRL_REC E2, PS_JOB G, PS_EMPLMT_SRCH_QRY G1, PS_NAMES H, PS_PERALL_SEC_QRY H1 
  WHERE ( D.DEPTID = A.DEPTID 
    AND D2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND D2.RECNAME = 'DEPT_TBL' 
    AND D2.SETID = D.SETID 
    AND C.JOBCODE = A.JOBCODE 
    AND C2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND C2.RECNAME = 'JOBCODE_TBL' 
    AND C2.SETID = C.SETID 
    AND E.LOCATION = A.LOCATION 
    AND E2.SETCNTRLVALUE = A.BUSINESS_UNIT 
    AND E2.RECNAME = 'LOCATION_TBL' 
    AND E2.SETID = E.SETID 
    AND A.EMPLID = A1.EMPLID 
    AND A.EMPL_RCD = A1.EMPL_RCD 
    AND A1.OPRID = 'XXXXXXXX' 
    AND G.EMPLID = G1.EMPLID 
    AND G.EMPL_RCD = G1.EMPL_RCD 
    AND G1.OPRID = 'XXXXXXXX' 
    AND H.EMPLID = H1.EMPLID 
    AND H1.OPRID = 'XXXXXXXX' 
    AND ( A.EFFDT = (SELECT MIN( F.EFFDT) 
  FROM PS_JOB F, PS_EMPLMT_SRCH_QRY F1 
  WHERE F.EMPLID = F1.EMPLID 
    AND F.EMPL_RCD = F1.EMPL_RCD 
    AND F1.OPRID = 'XXXXXXXX' 
    AND ( A.EMPLID = F.EMPLID 
     AND A.EMPL_RCD = F.EMPL_RCD 
     AND A.ACTION IN ('HIR','REH','REI') 
     AND A.EFFDT BETWEEN :1 AND :2 )) 
     AND A.ACTION IN ('HIR','REH','REI') 
     AND B.EMPLID = A.EMPLID 
     AND D.EFFDT = 
        (SELECT MAX(D_ED.EFFDT) FROM PS_DEPT_TBL D_ED 
        WHERE D.SETID = D_ED.SETID 
          AND D.DEPTID = D_ED.DEPTID 
          AND D_ED.EFFDT <= A.EFFDT) 
     AND C.EFFDT = 
        (SELECT MAX(C_ED.EFFDT) FROM PS_JOBCODE_TBL C_ED 
        WHERE C.SETID = C_ED.SETID 
          AND C.JOBCODE = C_ED.JOBCODE 
          AND C_ED.EFFDT <= A.EFFDT) 
     AND E.EFFDT = 
        (SELECT MAX(E_ED.EFFDT) FROM PS_LOCATION_TBL E_ED 
        WHERE E.SETID = E_ED.SETID 
          AND E.LOCATION = E_ED.LOCATION 
          AND E_ED.EFFDT <= A.EFFDT) 
     AND A.REPORTS_TO = G.POSITION_NBR 
     AND G.EMPLID = H.EMPLID 
     AND H.EFFDT = 
        (SELECT MAX(H_ED.EFFDT) FROM PS_NAMES H_ED 
        WHERE H.EMPLID = H_ED.EMPLID 
          AND H.NAME_TYPE = H_ED.NAME_TYPE) 
     AND G.EFFDT = 
        (SELECT MAX(G_ED.EFFDT) FROM PS_JOB G_ED 
        WHERE G.EMPLID = G_ED.EMPLID 
          AND G.EMPL_RCD = G_ED.EMPL_RCD 
          AND G_ED.EFFDT <= ( A.LAST_HIRE_DT)+1) 
    AND G.EFFSEQ = 
        (SELECT MAX(G_ES.EFFSEQ) FROM PS_JOB G_ES 
        WHERE G.EMPLID = G_ES.EMPLID 
          AND G.EMPL_RCD = G_ES.EMPL_RCD 
          AND G.EFFDT = G_ES.EFFDT) ))

Answer 1

此外，如果您的机构/公司只使用 SUPERVISOR_ID 字段，这会容易得多，看来您的问题是:

I seem to be bringing in all the managers who have been in the Reports_To position prior to the ending effective date

这是因为，在 PS_JOB(A) 上的初始选择中，对于在 ('HIR','REH',' REI')。

 AND ( A.EFFDT BETWEEN :1 AND :2  
 AND A.ACTION IN ('HIR','REH','REI')
 ... )

这将选择间隔中带有 EFFDT 的每个记录，而您只需要一个，大概是第一个(MIN)。

AND ( A.EFFDT = (SELECT MIN(A_ED.EFFDT) FROM PS_JOB A_ED
                    WHERE A_ED.EMPLID = A.EMPLID
                      AND A_ED.EMPL_RCD = A.EMPL_RCD
                      AND A.ED.EFFDT BETWEEN :1 AND :2
                      AND A_ED.ACTION IN ('HIR', 'REH', 'REI')
... ) 

这可能会解决问题，但请记住，您可能还必须考虑有效序列 (EFFSEQ) 以过滤掉一条记录。

编辑:通过查询管理器

转到标准并编辑 A.EFFDT 标准。

条件类型应为“等于”，表达式 2 类型应为子查询。

定义/编辑子查询

选择 Job EE 作为记录，在关于生效日期的消息框中单击 OK。

在查询选项卡下选择 EFFDT 作为字段，编辑字段并选择最小聚合。在这里，您会收到有关“聚合字段但被用于无条件的字段”的错误信息

转到 Criteria 选项卡，删除自动添加的 effdt 标准并添加其他标准，将子查询与主查询和 ACTION 标准相匹配。 1