I have a PeopleSoft query that is scheduled to run every day. The problem I am running into is that about half the time the query runs fine and sends me the xls spreadsheet, but the other half of the time I get a "Message=ORA-00936: missing expression" error. Below is my SQL, any help would be greatly appreciated. I am pretty new to queries and SQL and such, but I sort of got thrown into the fire when a co-worker quit last week...
Query SQL:
SELECT A.EMPLID, A.ACTION, A.ACTION_REASON, A.LASTUPDOPRID, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'JOB', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
FROM PS_JOB A, PS_C_EMPLMT_QRY_T1 A1
WHERE A.EMPLID = A1.EMPLID
AND A.EMPL_RCD = A1.EMPL_RCD
AND A1.OPRID = '450515'
AND 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 <= SYSDATE)
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.LASTUPDDTTM BETWEEN TRUNC(SYSDATE - 1) AND SYSDATE
AND A.LASTUPDOPRID IN ('450515','429409','455281','456129','471834','446915','471956','415218','456130','455282','024446','426613','395053')
AND A.EMPLID NOT IN (SELECT I.EMPLID
FROM PS_C_EAF I, PS_C_EMPLMT_QRY_T1 I1
WHERE I.EMPLID = I1.EMPLID
AND I.EMPL_RCD = I1.EMPL_RCD
AND I1.OPRID = '450515'
AND I.C_INSERTED_JOB_DT BETWEEN TRUNC(SYSDATE-1) AND SYSDATE
AND I.ACTION_REASON <> 'COS')
UNION
SELECT '', '', '', '', '', '', B.POSITION_NBR, B.ACTION, B.ACTION_REASON, B.LASTUPDOPRID, TO_CHAR(CAST((B.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'POSITION', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
FROM PS_POSITION_DATA B, PS_POSTN_SRCH_QRY B1, PS_JOB E, PS_C_EMPLMT_QRY_T1 E1
WHERE B.POSITION_NBR = B1.POSITION_NBR
AND B1.OPRID = '450515'
AND E.EMPLID = E1.EMPLID
AND E.EMPL_RCD = E1.EMPL_RCD
AND E1.OPRID = '450515'
AND B.LASTUPDDTTM BETWEEN TRUNC(SYSDATE - 1) AND SYSDATE
AND B.LASTUPDOPRID IN ('450515','429409','455281','456129','471834','446915','471953','415218','456130','455282','024446','426613','395053')
AND B.POSITION_NBR = E.POSITION_NBR
AND E.LAST_HIRE_DT <> B.EFFDT
AND E.ACTION NOT IN ('HIR','REH','ADD')
AND B.LASTUPDDTTM NOT BETWEEN E.LASTUPDDTTM - 7/86400 AND E.LASTUPDDTTM + 7/86400
AND E.HR_STATUS = 'A'
AND B.POSITION_NBR NOT IN (SELECT J.POSITION_NBR
FROM PS_C_EAF J, PS_C_EMPLMT_QRY_T1 J1
WHERE J.EMPLID = J1.EMPLID
AND J.EMPL_RCD = J1.EMPL_RCD
AND J1.OPRID = '450515'
AND J.POSITION_OVERRIDE = 'Y'
AND TRUNC( J.C_INSERTED_JOB_DT) = TRUNC( B.LASTUPDDTTM))
UNION
SELECT '', '', '', '', '', '', '', '', '', '', '', '', C.EMPLID, '', '', C.LASTUPDOPRID, TO_CHAR(CAST((C.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'ADDRESS', '', '', '', '', '', '', '', '', '', '', '', ''
FROM PS_ADDRESSES C, PS_EMPLMT_SRCH_QRY C1, PS_JOB F, PS_C_EMPLMT_QRY_T1 F1
WHERE C.EMPLID = C1.EMPLID
AND C1.OPRID = '450515'
AND F.EMPLID = F1.EMPLID
AND F.EMPL_RCD = F1.EMPL_RCD
AND F1.OPRID = '450515'
AND ( F.ACTION NOT IN ('HIR','REH','ADD')
OR F.LASTUPDDTTM NOT BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE+1))
AND C.LASTUPDDTTM BETWEEN TRUNC(SYSDATE - 1) AND SYSDATE
AND C.LASTUPDOPRID IN ('450515','429409','455281','456129','471834','446915','471956','415218','456130','455282','024446','426613','395053')
AND C.EMPLID = F.EMPLID
AND F.HR_STATUS = 'A'
AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_JOB F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.EMPL_RCD = F_ED.EMPL_RCD
AND F_ED.EFFDT <= C.LASTUPDDTTM)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_JOB F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.EMPL_RCD = F_ES.EMPL_RCD
AND F.EFFDT = F_ES.EFFDT)
UNION
SELECT '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', D.EMPLID, '', '', D.C_OPRID, TO_CHAR(D.LAST_UPDATE_DATE,'YYYY-MM-DD'), 'TAX', '', '', '', '', '', ''
FROM PS_FED_TAX_DATA D, PS_EMPLMT_SRCH_QRY D1
WHERE D.EMPLID = D1.EMPLID
AND D1.OPRID = '450515'
AND D.LAST_UPDATE_DATE BETWEEN TRUNC(SYSDATE - 1) AND SYSDATE
AND D.C_OPRID IN ('450515','429409','455281','456129','471834','446915','471956','415218','456130','455282','024446','426613','395053')
UNION
SELECT '', '', '', '', '', '', G.POSITION_NBR, G.ACTION, G.ACTION_REASON, G.LASTUPDOPRID, TO_CHAR(CAST((G.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'POSITION', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
FROM PS_POSITION_DATA G, PS_POSTN_SRCH_QRY G1
WHERE G.POSITION_NBR = G1.POSITION_NBR
AND G1.OPRID = '450515'
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_POSITION_DATA G_ED
WHERE G.POSITION_NBR = G_ED.POSITION_NBR
AND G_ED.EFFDT <= SYSDATE)
AND G.LASTUPDDTTM BETWEEN TRUNC(SYSDATE -1) AND SYSDATE
AND G.POSITION_NBR NOT IN (SELECT H.POSITION_NBR
FROM PS_JOB H, PS_C_EMPLMT_QRY_T1 H1
WHERE H.EMPLID = H1.EMPLID
AND H.EMPL_RCD = H1.EMPL_RCD
AND H1.OPRID = '450515'
AND G.EFF_STATUS = 'A')
AND G.LASTUPDOPRID IN ('450515','429409','455281','456129','471834','446915','471956','415218','456130','455282','024446','426613','395053')
UNION
SELECT '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', K.EMPLID, K.C_ACTION_EAF, K.ACTION_REASON, K.C_OPRID_EAF_APPRV1, TO_CHAR(CAST((K.C_INSERTED_JOB_DT) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), 'EAF'
FROM PS_C_EAF K, PS_C_EMPLMT_QRY_T1 K1
WHERE K.EMPLID = K1.EMPLID
AND K.EMPL_RCD = K1.EMPL_RCD
AND K1.OPRID = '450515'
AND K.C_OPRID_EAF_APPRV1 IN ('450515','429409','455281','456129','471834','446915','471956','415218','456130','455282','024446','426613','395053')
AND K.C_INSERTED_JOB_DT BETWEEN TRUNC(SYSDATE-1) AND SYSDATE
AND K.ACTION_REASON <> 'COS'
Thanks!
Edited by: 960121 on Sep 19, 2012 9:38 AM
Edited by: 960121 on Sep 19, 2012 9:39 AM
Edited by: 960121 on Sep 19, 2012 9:39 AM
Edited by: 960121 on Sep 19, 2012 9:40 AM