Good afternoon,

Let me start by stating that while I'm a DB person at my local hospital, I have never worked with Lawson data before. I've been requested to create an employee history report and thanks to the talented folks here, I have very nearly what I need.

Would someone be able to assist me in adding two fields to this query - I'm looking to add the employee's prior supervisor and current supervisor (much like the current position and prior position).

I believe the data I'm looking for is in the PAEMPPOS table, but I'm not entirely certain how to add it to this existing query.

SELECT *
FROM (
SELECT PAH.COMPANY
,EMP.PROCESS_LEVEL
,PAH.EMPLOYEE
,PAH.EFFECT_DATE
,PAH.ACTION_CODE
,PAH.REASON_01
,PAH.REASON_02
,EMP.LAST_NAME
,EMP.FIRST_NAME
,EMP.MIDDLE_INIT
,PEM.SEX
,PEM.EEO_CLASS
,EMP.POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) CUR_POS
WHERE CUR_POS.COMPANY = PAH.COMPANY
AND CUR_POS.EMPLOYEE = PAH.EMPLOYEE
AND CUR_POS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS CUR_POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 126
ORDER BY BEG_DATE DESC
) PREV_POSITION
WHERE PREV_POSITION.COMPANY = PAH.COMPANY
AND PREV_POSITION.EMPLOYEE = PAH.EMPLOYEE
AND PREV_POSITION.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS PREV_POSITION
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) CUR_STATUS
WHERE CUR_STATUS.COMPANY = PAH.COMPANY
AND CUR_STATUS.EMPLOYEE = PAH.EMPLOYEE
AND CUR_STATUS.BEG_DATE <= PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS CUR_STATUS
,COALESCE((
SELECT A_VALUE
FROM (
SELECT COMPANY
,EMPLOYEE
,BEG_DATE
,A_VALUE
FROM PRODLAW.HRHISTORY HRH
WHERE HRH.FLD_NBR = 20
ORDER BY BEG_DATE DESC
) PREV_STATUS
WHERE PREV_STATUS.COMPANY = PAH.COMPANY
AND PREV_STATUS.EMPLOYEE = PAH.EMPLOYEE
AND PREV_STATUS.BEG_DATE < PAH.EFFECT_DATE
AND rownum = 1
), ' ') AS PREV_STATUS
FROM PRODLAW.PERSACTHST PAH
LEFT OUTER JOIN PRODLAW.EMPLOYEE EMP ON (EMP.COMPANY = PAH.COMPANY)
AND (EMP.EMPLOYEE = PAH.EMPLOYEE)
LEFT OUTER JOIN PRODLAW.PAEMPLOYEE PEM ON (PEM.COMPANY = PAH.COMPANY)
AND (PEM.EMPLOYEE = PAH.EMPLOYEE)
WHERE (PAH.COMPANY = 1000)
AND (PAH.EFFECT_DATE BETWEEN to_date('2000-01-01', 'yyyy-mm-dd')
AND to_date('2016-12-31', 'yyyy-mm-dd')
)
)
WHERE CUR_POSITION <> PREV_POSITION
OR CUR_STATUS <> PREV_STATUS;

Thanks in advance for helping out a poor soul :)