#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    How to Reformat to get application prompt out of query


    We have a query that comes from peoplesoft. In the app, the user enters a date to run a report.

    My goal is to create a view, based on the query, that can then take the date and return the same rows as the report.

    I've highlighted where the parameter is entered. It's also used in the SELECT clause to determine a period of time. That has to be accounted for when the rows are returned. Any help would be appreciated.

    The original SQL is :

    SELECT A.BUSINESS_UNIT, A.COMPANY, A.DEPTID, E.DESCR, A.LOCATION, J.DESCR, A.EMPLID, A.EMPL_RCD, A.EMPL_STATUS, A.HIRE_DT, A.LAST_HIRE_DT, H.SERVICE_DT, H.PROF_EXPERIENCE_DT,

    DECIMAL(ROUND((DAYS(':1') - DAYS(H.SERVICE_DT)) / 365.25,+2), 7, 2),

    A.EMPL_CLASS, A.JOBCODE, F.DESCR, F.MANAGER_LEVEL, F.FLSA_STATUS, F.US_OCC_CD, A.POSITION_NBR, A.SHIFT, A.REG_TEMP, A.FULL_PART_TIME, A.EMPL_TYPE, A.STD_HOURS, A.SAL_ADMIN_PLAN, A.GRADE, A.ANNUAL_RT, A.HOURLY_RT, A.FTE, G.MIN_RT_HOURLY, G.MID_RT_HOURLY, G.MAX_RT_HOURLY, I.NAME_PSFORMAT,

    DECIMAL(ROUND(( A.HOURLY_RT/ G.MID_RT_HOURLY),2), 5, 2),

    C.NAME_PSFORMAT, D.DESCR, B.EMPLID, A.REPORTS_TO

    FROM ((PS_JOB A LEFT OUTER JOIN
    PS_JOB B ON
    A.REPORTS_TO = B.POSITION_NBR
    AND
    B.EMPL_STATUS IN ('A','L','P')
    )

    LEFT OUTER JOIN
    PS_PERSON_NAME C ON
    B.EMPLID = C.EMPLID
    ),
    PS_POSITION_VIEW D,
    PS_DEPT_TBL E,
    PS_JOBCODE_TBL F,
    PS_SAL_GRADE_TBL G,
    PS_PER_ORG_ASGN H,
    PS_PERSON_NAME I,
    PS_LOCATION_TBL J

    WHERE (
    A.EFFDT =
    (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
    WHERE A.EMPLID = A_ED.EMPLID
    AND A.EMPL_RCD = A_ED.EMPL_RCD
    AND A_ED.EFFDT <=
    Code:
    <b>:1</b>

    )
    AND A.EFFSEQ =
    (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
    WHERE A.EMPLID = A_ES.EMPLID
    AND A.EMPL_RCD = A_ES.EMPL_RCD
    AND A.EFFDT = A_ES.EFFDT
    )
    AND A.EMPL_STATUS IN ('A','L','P')
    AND (B.EFFDT =
    (SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
    WHERE B.EMPLID = B_ED.EMPLID
    AND B.EMPL_RCD = B_ED.EMPL_RCD
    AND B_ED.EFFDT <= A.EFFDT
    )
    AND B.EFFSEQ =
    (SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
    WHERE B.EMPLID = B_ES.EMPLID
    AND B.EMPL_RCD = B_ES.EMPL_RCD
    AND B.EFFDT = B_ES.EFFDT
    )
    OR B.EFFDT IS NULL
    )

    AND D.POSITION_NBR = A.REPORTS_TO
    AND D.EFFDT =
    (
    SELECT MAX(D_ED.EFFDT) FROM PS_POSITION_VIEW D_ED
    WHERE D.POSITION_NBR = D_ED.POSITION_NBR
    AND D_ED.EFFDT <= A.EFFDT
    )
    AND E.DEPTID = A.DEPTID
    AND E.EFFDT =
    (
    SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED
    WHERE E.SETID = E_ED.SETID
    AND E.DEPTID = E_ED.DEPTID
    AND E_ED.EFFDT <= CURRENT DATE)
    AND E.SETID = A.SETID_DEPT
    AND F.JOBCODE = A.JOBCODE
    AND F.EFFDT =
    (SELECT MAX(F_ED.EFFDT) FROM PS_JOBCODE_TBL F_ED
    WHERE F.SETID = F_ED.SETID
    AND F.JOBCODE = F_ED.JOBCODE
    AND F_ED.EFFDT <= A.EFFDT)
    AND F.SETID = A.SETID_JOBCODE
    AND G.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN
    AND G.GRADE = A.GRADE
    AND G.EFFDT =
    (SELECT MAX(G_ED.EFFDT) FROM PS_SAL_GRADE_TBL G_ED
    WHERE G.SETID = G_ED.SETID
    AND G.SAL_ADMIN_PLAN = G_ED.SAL_ADMIN_PLAN
    AND G.GRADE = G_ED.GRADE
    AND G_ED.EFFDT <= A.EFFDT)
    AND G.SETID = A.SETID_SALARY
    AND A.EMPLID = H.EMPLID
    AND A.EMPL_RCD = H.EMPL_RCD
    AND A.EMPLID = I.EMPLID
    AND J.LOCATION = A.LOCATION
    AND J.EFFDT =
    (SELECT MAX(J_ED.EFFDT) FROM PS_LOCATION_TBL J_ED
    WHERE J.SETID = J_ED.SETID
    AND J.LOCATION = J_ED.LOCATION
    AND J_ED.EFFDT <= A.EFFDT)
    AND J.SETID = A.SETID_LOCATION )
    ORDER BY 7, 8
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    2
    Rep Power
    0

    Additionally


    I've taken a couple stabs at this. But comparing results, for a <= 01/01/2010 that returns ~8700 in the app, mine returned 250,000. So I know something's wrong, just don't know where to start over.

IMN logo majestic logo threadwatch logo seochat tools logo