PeopleSoft: A SQL error occured. Please consult your system log for details

Question

While clicking HTML link on my PS Query, I get the error: A SQL error occured. Please consult your system log for details. I want to know what the cause of this error, and if its my PS Query, since it's very straight forward. See SQL below:

SELECT A.EMPLID, A.ACAD_CAREER, A.STDNT_CAR_NBR, A.ADM_APPL_NBR, 
A.INSTITUTION, A.SAD_PB_CAS_STATDT
  FROM PS_SAD_PB_CAS A, PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1, 
PS_SCC_PS_ADMA_BND C, PS_ADM_APPL_SCTY C1 
  WHERE ( B.EMPLID = B1.EMPLID 
    AND B.ACAD_CAREER = B1.ACAD_CAREER 
    AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR 
    AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR 
    AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR 
    AND C.EMPLID = C1.EMPLID 
    AND C.ACAD_CAREER = C1.ACAD_CAREER 
    AND C.ADM_APPL_NBR = C1.ADM_APPL_NBR 
    AND C.INSTITUTION = C1.INSTITUTION 
    AND ( A.EFFDT = 
        (SELECT MAX(A_ED.EFFDT) FROM PS_SAD_PB_CAS A_ED 
        WHERE A.EMPLID = A_ED.EMPLID 
          AND A.INSTITUTION = A_ED.INSTITUTION 
          AND A.SAD_PB_REC_TYPE = A_ED.SAD_PB_REC_TYPE 
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER 
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR 
          AND A.SAD_PB_REC_NUMBER = A_ED.SAD_PB_REC_NUMBER 
          AND A.ADM_APPL_NBR = A_ED.ADM_APPL_NBR 
          AND A.APPL_PROG_NBR = A_ED.APPL_PROG_NBR 
          AND A_ED.EFFDT <= SYSDATE) 
    AND A.EFFSEQ = 
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_SAD_PB_CAS A_ES 
        WHERE A.EMPLID = A_ES.EMPLID 
          AND A.INSTITUTION = A_ES.INSTITUTION 
          AND A.SAD_PB_REC_TYPE = A_ES.SAD_PB_REC_TYPE 
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER 
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR 
          AND A.SAD_PB_REC_NUMBER = A_ES.SAD_PB_REC_NUMBER 
          AND A.ADM_APPL_NBR = A_ES.ADM_APPL_NBR 
          AND A.APPL_PROG_NBR = A_ES.APPL_PROG_NBR 
          AND A.EFFDT = A_ES.EFFDT) 
     AND A.EMPLID = B.EMPLID 
     AND A.ACAD_CAREER = B.ACAD_CAREER 
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR 
     AND A.ADM_APPL_NBR = B.ADM_APPL_NBR 
     AND A.APPL_PROG_NBR = B.APPL_PROG_NBR 
     AND B.EFFDT = 
        (SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_PROG B_ED 
        WHERE B.EMPLID = B_ED.EMPLID 
          AND B.ACAD_CAREER = B_ED.ACAD_CAREER 
          AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR 
          AND B.ADM_APPL_NBR = B_ED.ADM_APPL_NBR 
          AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR 
          AND B_ED.EFFDT <= A.EFFDT) 
    AND B.EFFSEQ = 
        (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_PROG B_ES 
        WHERE B.EMPLID = B_ES.EMPLID 
           AND B.ACAD_CAREER = B_ES.ACAD_CAREER 
           AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR 
           AND B.ADM_APPL_NBR = B_ES.ADM_APPL_NBR 
           AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR 
           AND B.EFFDT = B_ES.EFFDT) 
     AND A.EMPLID = C.EMPLID 
     AND A.INSTITUTION = C.INSTITUTION 
     AND A.ACAD_CAREER = C.ACAD_CAREER 
     AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR 
     AND A.ADM_APPL_NBR = C.ADM_APPL_NBR 
     AND to_date( A.SAD_PB_CAS_STATDT,'DD-MON-YY') = to_date(sysdate,'DD-MON-YY') 
     AND A.SAD_PB_CAS_STATUS IN ('ASG','USD') ));

Can anyone help me out with this error? Thank you so much.

Answer 1

This part looks wrong:

to_date(a.sad_pb_cas_statdt,'DD-MON-YY') = to_date(sysdate,'DD-MON-YY')

sysdate is already a date (and probably so is sad_pb_cas_statdt), so applying any character function to it is asking for trouble. If nls_date_format is set to anything that is not compatible with the specified format 'DD-MON-YY', it will fail. (For example, you will get away with 'DD-MON-RRRR', but not YYYY-MM-DD.)

Try changing it to:

a.sad_pb_cas_statdt >= trunc(sysdate) and a.sad_pb_cas_statdt < trunc(sysdate) +1

or

a.sad_pb_cas_statdt between trunc(sysdate) and trunc(sysdate) + interval '86399' second