Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
SQL Error [ ORA-00936: missing expression
Hi
After running the workflow it's giving ORA 00936 error in the session log of Workflow monitor. The following query gives the error. As a result my execution plan in the DAC fails. Any help will be appreciated.
SELECT
ASG.ASSIGNMENT_ID
,CASE WHEN ASG.EFFECTIVE_START_DATE >
THEN ASG.EFFECTIVE_START_DATE
ELSE
END
,ASG.EVENT_TYPE
,ASG.PERSON_ID
,ASG.ORGANIZATION_ID
,ASG.ORGANIZATION_PRV_ID
,ASG.JOB_ID
,ASG.JOB_PRV_ID
,ASG.PAY_GRADE_ID
,ASG.PAY_GRADE_PRV_ID
,ASG.POSITION_ID
,ASG.POSITION_PRV_ID
,ASG.LOCATION_ID
,ASG.LOCATION_PRV_ID
,ASG.SUPERVISOR_ID
,ASG.SUPERVISOR_PRV_ID
,ASG.ASSIGNMENT_STATUS_TYPE_ID
,PTYP.PERSON_TYPE_ID
,ASG.ASSIGNMENT_TYPE
,ASG.ASSIGNMENT_NUMBER
,ASG.PRIMARY_FLAG
,ASG.PAY_BASIS
,ASG.JOB_FLSA_CODE
,ASG.EMPLOYMENT_CATEGORY
,ASG.UNION_MEMBER_FLAG
,ASG.BUSINESS_GROUP_ID
,ASG.CHANGE_REASON_CODE
,ASG.CHANGE_REASON_TYPE
,SAL.CURRENCY_CODE
,ASG.HIRE_DATE_ORIG
,ASG.HIRE_DATE
,ASG.HIRE_DATE_ADJ
,ASG.DATE_OF_BIRTH
,ASG.ORG_ENTRY_DATE
,ASG.JOB_ENTRY_DATE
,ASG.POS_ENTRY_DATE
,ASG.GRD_ENTRY_DATE
,ASG.LAST_PROM_DATE
,SAL.LAST_SAL_INCR_DT
,HDC.HEADCOUNT
,FTE.FTE
,SAL.SALARY
,SAL.ANNUALIZATION_FACTOR
,PERF.PERF_NRMLZD_RATING
,PERF.PERF_RATING
,ASG.NORMAL_HOURS_MONTH
,ASG.POW_DAYS_ALL
,ASG.EMP_IND
,ASG.CWK_IND
,ASG.MANAGER_FLAG
,SPVST.SUPERVISOR_FLAG
,ASG.FIRST_RECORD_IND
,ASG.HIRE_EVENT_IND
,ASG.REHIRE_EVENT_IND
,ASG.TERM_EVENT_IND
,ASG.ASG_START_EVENT_IND
,ASG.ASG_END_EVENT_IND
,ASG.ORG_CHANGE_IND
,ASG.JOB_CHANGE_IND
,ASG.POS_CHANGE_IND
,ASG.GRD_CHANGE_IND
,ASG.LOC_CHANGE_IND
,ASG.SUP_CHANGE_IND
,ASG.ASG_STATUS_CHANGE_IND
,ASG.PROMOTION_EVENT_IND
,ASG.TRANSFER_EVENT_IND
,ASG.W_EVENT_GRP_CODE
,ASG.W_EVENT_SUBG_CODE
,ASG.EVENT_CODE
,ASG.DATASOURCE_NUM_ID
,'1' X_CUSTOM
FROM
W_ORA_WEVT_ASG_PS ASG
LEFT OUTER JOIN W_ORA_WEVT_PERF_PS PERF
ON ASG.ASSIGNMENT_ID = PERF.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = PERF.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_SAL_PS SAL
ON ASG.ASSIGNMENT_ID = SAL.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = SAL.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SAL.EFFECTIVE_START_DATE
AND SAL.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_HDC_PS HDC
ON ASG.ASSIGNMENT_ID = HDC.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = HDC.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN HDC.EFFECTIVE_START_DATE
AND HDC.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_FTE_PS FTE
ON ASG.ASSIGNMENT_ID = FTE.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = FTE.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN FTE.EFFECTIVE_START_DATE
AND FTE.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_SUPV_STATUS_PS SPVST
ON ASG.PERSON_ID = SPVST.PERSON_ID AND
ASG.DATASOURCE_NUM_ID = SPVST.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SPVST.EFFECTIVE_START_DATE
AND SPVST.EFFECTIVE_END_DATE
,W_ORA_WEVT_PTYP_PS PTYP
WHERE ASG.ASSIGNMENT_ID = PTYP.ASSIGNMENT_ID
AND ASG.DATASOURCE_NUM_ID = PTYP.DATASOURCE_NUM_ID
AND ASG.EVENT_DATE BETWEEN PTYP.EFFECTIVE_START_DATE
AND PTYP.EFFECTIVE_END_DATE
AND ASG.WRKFC_CHANGE_IND = 1
AND ASG.EFFECTIVE_END_DATE >= ]
2016-03-19 11:43:23 : INFO : (28272 | READER_1_1_1) : (IS | INFA_PROD_INTGRS) : node01_sbiap01 : RR_4049 : RR_4049 SQL Query issued to database : (Sat Mar 19 11:43:23 2016)
2016-03-19 11:43:23 : ERROR : (28272 | READER_1_1_1) : (IS | INFA_PROD_INTGRS) : node01_sbiap01 : RR_4035 : SQL Error [
ORA-00936: missing expression
Database driver error...
Function Name : Execute
SQL Stmt : SELECT
ASG.ASSIGNMENT_ID
,CASE WHEN ASG.EFFECTIVE_START_DATE >
THEN ASG.EFFECTIVE_START_DATE
ELSE
END
,ASG.EVENT_TYPE
,ASG.PERSON_ID
,ASG.ORGANIZATION_ID
,ASG.ORGANIZATION_PRV_ID
,ASG.JOB_ID
,ASG.JOB_PRV_ID
,ASG.PAY_GRADE_ID
,ASG.PAY_GRADE_PRV_ID
,ASG.POSITION_ID
,ASG.POSITION_PRV_ID
,ASG.LOCATION_ID
,ASG.LOCATION_PRV_ID
,ASG.SUPERVISOR_ID
,ASG.SUPERVISOR_PRV_ID
,ASG.ASSIGNMENT_STATUS_TYPE_ID
,PTYP.PERSON_TYPE_ID
,ASG.ASSIGNMENT_TYPE
,ASG.ASSIGNMENT_NUMBER
,ASG.PRIMARY_FLAG
,ASG.PAY_BASIS
,ASG.JOB_FLSA_CODE
,ASG.EMPLOYMENT_CATEGORY
,ASG.UNION_MEMBER_FLAG
,ASG.BUSINESS_GROUP_ID
,ASG.CHANGE_REASON_CODE
,ASG.CHANGE_REASON_TYPE
,SAL.CURRENCY_CODE
,ASG.HIRE_DATE_ORIG
,ASG.HIRE_DATE
,ASG.HIRE_DATE_ADJ
,ASG.DATE_OF_BIRTH
,ASG.ORG_ENTRY_DATE
,ASG.JOB_ENTRY_DATE
,ASG.POS_ENTRY_DATE
,ASG.GRD_ENTRY_DATE
,ASG.LAST_PROM_DATE
,SAL.LAST_SAL_INCR_DT
,HDC.HEADCOUNT
,FTE.FTE
,SAL.SALARY
,SAL.ANNUALIZATION_FACTOR
,PERF.PERF_NRMLZD_RATING
,PERF.PERF_RATING
,ASG.NORMAL_HOURS_MONTH
,ASG.POW_DAYS_ALL
,ASG.EMP_IND
,ASG.CWK_IND
,ASG.MANAGER_FLAG
,SPVST.SUPERVISOR_FLAG
,ASG.FIRST_RECORD_IND
,ASG.HIRE_EVENT_IND
,ASG.REHIRE_EVENT_IND
,ASG.TERM_EVENT_IND
,ASG.ASG_START_EVENT_IND
,ASG.ASG_END_EVENT_IND
,ASG.ORG_CHANGE_IND
,ASG.JOB_CHANGE_IND
,ASG.POS_CHANGE_IND
,ASG.GRD_CHANGE_IND
,ASG.LOC_CHANGE_IND
,ASG.SUP_CHANGE_IND
,ASG.ASG_STATUS_CHANGE_IND
,ASG.PROMOTION_EVENT_IND
,ASG.TRANSFER_EVENT_IND
,ASG.W_EVENT_GRP_CODE
,ASG.W_EVENT_SUBG_CODE
,ASG.EVENT_CODE
,ASG.DATASOURCE_NUM_ID
,'1' X_CUSTOM
FROM
W_ORA_WEVT_ASG_PS ASG
LEFT OUTER JOIN W_ORA_WEVT_PERF_PS PERF
ON ASG.ASSIGNMENT_ID = PERF.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = PERF.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_SAL_PS SAL
ON ASG.ASSIGNMENT_ID = SAL.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = SAL.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SAL.EFFECTIVE_START_DATE
AND SAL.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_HDC_PS HDC
ON ASG.ASSIGNMENT_ID = HDC.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = HDC.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN HDC.EFFECTIVE_START_DATE
AND HDC.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_FTE_PS FTE
ON ASG.ASSIGNMENT_ID = FTE.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = FTE.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN FTE.EFFECTIVE_START_DATE
AND FTE.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_SUPV_STATUS_PS SPVST
ON ASG.PERSON_ID = SPVST.PERSON_ID AND
ASG.DATASOURCE_NUM_ID = SPVST.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SPVST.EFFECTIVE_START_DATE
AND SPVST.EFFECTIVE_END_DATE
,W_ORA_WEVT_PTYP_PS PTYP
WHERE ASG.ASSIGNMENT_ID = PTYP.ASSIGNMENT_ID
AND ASG.DATASOURCE_NUM_ID = PTYP.DATASOURCE_NUM_ID
AND ASG.EVENT_DATE BETWEEN PTYP.EFFECTIVE_START_DATE
AND PTYP.EFFECTIVE_END_DATE
AND ASG.WRKFC_CHANGE_IND = 1
AND ASG.EFFECTIVE_END_DATE >=
Oracle Fatal Error
Database driver error...
Function Name : Execute
SQL Stmt : SELECT
ASG.ASSIGNMENT_ID
,CASE WHEN ASG.EFFECTIVE_START_DATE >
THEN ASG.EFFECTIVE_START_DATE
ELSE
END
,ASG.EVENT_TYPE
,ASG.PERSON_ID
,ASG.ORGANIZATION_ID
,ASG.ORGANIZATION_PRV_ID
,ASG.JOB_ID
,ASG.JOB_PRV_ID
,ASG.PAY_GRADE_ID
,ASG.PAY_GRADE_PRV_ID
,ASG.POSITION_ID
,ASG.POSITION_PRV_ID
,ASG.LOCATION_ID
,ASG.LOCATION_PRV_ID
,ASG.SUPERVISOR_ID
,ASG.SUPERVISOR_PRV_ID
,ASG.ASSIGNMENT_STATUS_TYPE_ID
,PTYP.PERSON_TYPE_ID
,ASG.ASSIGNMENT_TYPE
,ASG.ASSIGNMENT_NUMBER
,ASG.PRIMARY_FLAG
,ASG.PAY_BASIS
,ASG.JOB_FLSA_CODE
,ASG.EMPLOYMENT_CATEGORY
,ASG.UNION_MEMBER_FLAG
,ASG.BUSINESS_GROUP_ID
,ASG.CHANGE_REASON_CODE
,ASG.CHANGE_REASON_TYPE
,SAL.CURRENCY_CODE
,ASG.HIRE_DATE_ORIG
,ASG.HIRE_DATE
,ASG.HIRE_DATE_ADJ
,ASG.DATE_OF_BIRTH
,ASG.ORG_ENTRY_DATE
,ASG.JOB_ENTRY_DATE
,ASG.POS_ENTRY_DATE
,ASG.GRD_ENTRY_DATE
,ASG.LAST_PROM_DATE
,SAL.LAST_SAL_INCR_DT
,HDC.HEADCOUNT
,FTE.FTE
,SAL.SALARY
,SAL.ANNUALIZATION_FACTOR
,PERF.PERF_NRMLZD_RATING
,PERF.PERF_RATING
,ASG.NORMAL_HOURS_MONTH
,ASG.POW_DAYS_ALL
,ASG.EMP_IND
,ASG.CWK_IND
,ASG.MANAGER_FLAG
,SPVST.SUPERVISOR_FLAG
,ASG.FIRST_RECORD_IND
,ASG.HIRE_EVENT_IND
,ASG.REHIRE_EVENT_IND
,ASG.TERM_EVENT_IND
,ASG.ASG_START_EVENT_IND
,ASG.ASG_END_EVENT_IND
,ASG.ORG_CHANGE_IND
,ASG.JOB_CHANGE_IND
,ASG.POS_CHANGE_IND
,ASG.GRD_CHANGE_IND
,ASG.LOC_CHANGE_IND
,ASG.SUP_CHANGE_IND
,ASG.ASG_STATUS_CHANGE_IND
,ASG.PROMOTION_EVENT_IND
,ASG.TRANSFER_EVENT_IND
,ASG.W_EVENT_GRP_CODE
,ASG.W_EVENT_SUBG_CODE
,ASG.EVENT_CODE
,ASG.DATASOURCE_NUM_ID
,'1' X_CUSTOM
FROM
W_ORA_WEVT_ASG_PS ASG
LEFT OUTER JOIN W_ORA_WEVT_PERF_PS PERF
ON ASG.ASSIGNMENT_ID = PERF.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = PERF.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN PERF.EFFECTIVE_START_DATE
AND PERF.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_SAL_PS SAL
ON ASG.ASSIGNMENT_ID = SAL.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = SAL.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SAL.EFFECTIVE_START_DATE
AND SAL.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_HDC_PS HDC
ON ASG.ASSIGNMENT_ID = HDC.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = HDC.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN HDC.EFFECTIVE_START_DATE
AND HDC.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_WEVT_FTE_PS FTE
ON ASG.ASSIGNMENT_ID = FTE.ASSIGNMENT_ID AND
ASG.DATASOURCE_NUM_ID = FTE.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN FTE.EFFECTIVE_START_DATE
AND FTE.EFFECTIVE_END_DATE
LEFT OUTER JOIN W_ORA_SUPV_STATUS_PS SPVST
ON ASG.PERSON_ID = SPVST.PERSON_ID AND
ASG.DATASOURCE_NUM_ID = SPVST.DATASOURCE_NUM_ID AND
ASG.EVENT_DATE BETWEEN SPVST.EFFECTIVE_START_DATE
AND SPVST.EFFECTIVE_END_DATE
,W_ORA_WEVT_PTYP_PS PTYP
WHERE ASG.ASSIGNMENT_ID = PTYP.ASSIGNMENT_ID
AND ASG.DATASOURCE_NUM_ID = PTYP.DATASOURCE_NUM_ID
AND ASG.EVENT_DATE BETWEEN PTYP.EFFECTIVE_START_DATE
AND PTYP.EFFECTIVE_END_DATE
AND ASG.WRKFC_CHANGE_IND = 1
Regards
Vyshak
Answers
-
From the Error, it looks like Effective start and End date is not set properly. See to it variables are properly initialized and given value.
0 -
Check the session parameter file exists or not with that variable?
is it very fresh load or error from stable environment?
0