SAP (Documents/Screen shots/Complete Solution): RE:[peopletools-l] Max Completion Date

Reply from malmoula on Jan 3 at 5:05 PM

Thanks. I tried the above and got the following error. Any idea what it means?

Error in running query because of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380)

Query SQL:
SELECT F.LM_HR_EMPLID, G.NAME, A.LM_HR_EMPLID, B.NAME, C.LM_PER_ORG, D.LM_STTS, TO_CHAR(D.LM_ENRL_DT,'YYYY-MM-DD'), TO_CHAR(D.LM_COMPL_DT,'YYYY-MM-DD'), E.LM_CS_LONG_NM, E.LM_CRSE_CODE, TO_CHAR(SYSDATE,'YYYY-MM-DD'),E.LM_CI_ID,TO_CHAR(E.EFFDT,'YYYY-MM-DD')
FROM PS_LM_PERSON A, PS_LM_PERSON_NAME B, PS_LM_PERSON_ATTRB C, PS_LM_ENRLMT D, PS_LM_CI_TBL E, PS_LM_PERSON F, PS_LM_PERSON_NAME G
WHERE A.LM_PERSON_ID = B.LM_PERSON_ID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_LM_PERSON_NAME B_ED
WHERE B.LM_PERSON_ID = B_ED.LM_PERSON_ID
AND B.LM_NAME_TYPE = B_ED.LM_NAME_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND B.LM_PERSON_ID = C.LM_PERSON_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_LM_PERSON_ATTRB C_ED
WHERE C.LM_PERSON_ID = C_ED.LM_PERSON_ID
AND C.LM_EMPL_RCD = C_ED.LM_EMPL_RCD
AND C_ED.EFFDT <= SYSDATE)
AND C.LM_PERSON_ID = D.LM_PERSON_ID
AND C.LM_EMPL_RCD = D.LM_EMPL_RCD
AND E.LM_CI_ID = D.LM_CI_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_LM_CI_TBL E_ED
WHERE E.LM_CI_ID = E_ED.LM_CI_ID
AND E_ED.EFFDT <= SYSDATE)
AND F.LM_PERSON_ID = C.LM_MANAGER_ID
AND F.LM_PERSON_ID = G.LM_PERSON_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_LM_PERSON_NAME G_ED
WHERE G.LM_PERSON_ID = G_ED.LM_PERSON_ID
AND G.LM_NAME_TYPE = G_ED.LM_NAME_TYPE
AND G_ED.EFFDT <= SYSDATE)
AND D.LM_COMPL_DT = (SELECT MAX( H.LM_COMPL_DT)
FROM PS_LM_ENRLMT H
WHERE D.LM_PERSON_ID = H.LM_PERSON_ID
AND H.LM_CI_ID = (SELECT I.LM_CI_ID
FROM PS_LM_CI_TBL I
WHERE I.EFFDT =
(SELECT MAX(I_ED.EFFDT) FROM PS_LM_CI_TBL I_ED
WHERE I.LM_CI_ID = I_ED.LM_CI_ID
AND I_ED.EFFDT <= SYSDATE)
AND I.LM_CI_ID = :1
AND I.LM_CI_ID = H.LM_CI_ID)
HAVING MAX( H.LM_COMPL_DT) BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD'))
AND E.LM_CRSE_CODE = :1
ORDER BY 2, 3, 8

---------------Original Message---------------
From: malmoula
Sent: Thursday, December 29, 2011 2:47 PM
Subject: Max Completion Date

Working on modifying a query in PS/ELM Query Manager

This report is for a specific course and lists out all employees who completed the course in a defined date range.
We would like the query to display only one record per employee, where the employee has taken / completed the course 3 times between the dates.
The last / most current completion date should be included and not the other 2 dates.

For example if am running the query for course XYZ and Emp A has taken the course 3 times this year - 02/01/2011, 06/01/2011 and 11/01/2011, we would like to show only the 11/01/2011 row.

Anyone has any ideas to make it work?

Here is the Query SQL:

SELECT F.LM_HR_EMPLID, G.NAME, A.LM_HR_EMPLID, B.NAME, C.LM_PER_ORG, D.LM_STTS, TO_CHAR(D.LM_ENRL_DT,'YYYY-MM-DD'), TO_CHAR(D.LM_COMPL_DT,'YYYY-MM-DD'), E.LM_CS_LONG_NM, E.LM_CRSE_CODE, TO_CHAR(SYSDATE,'YYYY-MM-DD'),E.LM_CI_ID,TO_CHAR(E.EFFDT,'YYYY-MM-DD')
FROM PS_LM_PERSON A, PS_LM_PERSON_NAME B, PS_LM_PERSON_ATTRB C, PS_LM_ENRLMT D, PS_LM_CI_TBL E, PS_LM_PERSON F, PS_LM_PERSON_NAME G
WHERE A.LM_PERSON_ID = B.LM_PERSON_ID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_LM_PERSON_NAME B_ED
WHERE B.LM_PERSON_ID = B_ED.LM_PERSON_ID
AND B.LM_NAME_TYPE = B_ED.LM_NAME_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND B.LM_PERSON_ID = C.LM_PERSON_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_LM_PERSON_ATTRB C_ED
WHERE C.LM_PERSON_ID = C_ED.LM_PERSON_ID
AND C.LM_EMPL_RCD = C_ED.LM_EMPL_RCD
AND C_ED.EFFDT <= SYSDATE)
AND C.LM_PERSON_ID = D.LM_PERSON_ID
AND C.LM_EMPL_RCD = D.LM_EMPL_RCD
AND E.LM_CI_ID = D.LM_CI_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_LM_CI_TBL E_ED
WHERE E.LM_CI_ID = E_ED.LM_CI_ID
AND E_ED.EFFDT <= SYSDATE)
AND F.LM_PERSON_ID = C.LM_MANAGER_ID
AND F.LM_PERSON_ID = G.LM_PERSON_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_LM_PERSON_NAME G_ED
WHERE G.LM_PERSON_ID = G_ED.LM_PERSON_ID
AND G.LM_NAME_TYPE = G_ED.LM_NAME_TYPE
AND G_ED.EFFDT <= SYSDATE)
AND D.LM_COMPL_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')
AND E.LM_CRSE_CODE = :1
AND D.LM_COMPL_DT = (SELECT MAX( I.LM_COMPL_DT)
FROM PS_LM_ENRLMT I
HAVING COUNT( I.LM_ACT_ID) > 1)
ORDER BY 2, 3, 8

Thanks a lot for your time.

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback