Getting SQL error while modifying steps in Approval Process Definition with MSSQL server (Doc ID 2200900.1)

Last updated on JANUARY 05, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
PeopleSoft Enterprise FIN Payables - Version 9.2 and later
PeopleSoft Enterprise ELM Enterprise Learning Management - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Receiving a SQL error when trying to add or modify an approval process with MSSQL database:


Error Position: 0

  Return: 8601 - [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'LNG'.
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHERE'.
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could n

  Statement: UPDATE PS_EOAW_STEP_LNG LNG SET LNG.EOAWSTEP_NBR = ( SELECT BASE.SEQ_NBR FROM PS_EOAW_STEP BASE WHERE BASE.EOAWPRCS_ID = LNG.EOAWPRCS_ID AND BASE.EOAWDEFN_ID = LNG.EOAWDEFN_ID AND BASE.EFFDT = LNG.EFFDT AND BASE.EOAWSTAGE_NBR = LNG.EOAWSTAGE_NBR AND BASE.EOAWPATH_ID = LNG.EOAWPATH_ID AND BASE.EOAWSTEP_NBR = LNG.EOAWSTEP_NBR) WHERE LNG.EOAWPRCS_ID = :1 AND LNG.EOAWDEFN_ID = :2 AND LNG.EFFDT = :3

  Original Statement: UPDATE PS_EOAW_STEP_LNG LNG SET LNG.EOAWSTEP_NBR = ( SELECT BASE.SEQ_NBR FROM PS_EOAW_STEP BASE WHERE BASE.EOAWPRCS_ID = LNG.EOAWPRCS_ID AND BASE.EOAWDEFN_ID = LNG.EOAWDEFN_ID AND BASE.EFFDT = LNG.EFFDT AND BASE.EOAWSTAGE_NBR = LNG.EOAWSTAGE_NBR AND BASE.EOAWPATH_ID = LNG.EOAWPATH_ID AND BASE.EOAWSTEP_NBR = LNG.EOAWSTEP_NBR) WHERE LNG.EOAWPRCS_ID = :1 AND LNG.EOAWDEFN_ID = :2 AND LNG.EFFDT = %DateIn(:3)
PSAPPSRV.7108 (347) 1-4973976 10.32.03 0.000000 ErrorReturn-> 280 - SQL error in Exec. (2,280) EOAW_UI.ProcessController.OnExecute Name:stepComponentSavePostChange PCPC:29351 Statement:413

Called from:EOAW_PRCS.GBL.SavePostChange Statement:1
PSAPPSRV.7108 (347) 1-4973977 10.32.03 0.001000 <<< end-ext Nest=01 stepComponentSavePostChange EOAW_UI.ProcessController.OnExecute Dur=0.019458 CPU=0.015625 Cycles=45
PSAPPSRV.7108 (347) 1-4973978 10.32.03 0.000000 <<< end Nest=00 EOAW_PRCS.GBL.SavePostChange Dur=0.062744 CPU=0.062500 Cycles=50

Replication steps

  1. Navigate to Main menu - Enterprise Components - Approval - Approvals - Approval Process Setup
  2. Add a new process or select an existing one to modify.
  3. Make some modifications to the steps; the one that most commonly triggers the error is re-ordering the steps
  4. Attempt to Save the page, and the error will be thrown.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms