Reply from David on Jan 3 at 5:20 PM If I recall things correctly, that error message means you are comparing an numeric field with a non-numeric field or constant. Compare the fields on the new lines you've added to make sure that the field types are the same. What I see off the top is the line:
AND I.LM_CI_ID = :1
You are also using :1 against the E.LM_CRSE_CODE field. Make sure that both the LM_CRSE_CODE and LM_CI_ID fields are the same type. And if so, why not just change the above line to:
AND I.LM_CI_ID = E.LM_CRSE_CODE
Just a thought. I don't personally have access to these fields so I can't look myself. I'm guessing it's part of HMRS?
D
| | | ---------------Original Message---------------
From: malmoula
Sent: Tuesday, January 03, 2012 5:06 PM
Subject: Max Completion Date
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 |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment