Error: ORA-00936: missing expression - during Execute Segmentation job

Some segments in an appeal may not produce any output when the Execute Segmentation is run. When reviewing the log, this error may be found: ORA-00936: missing expression.  Here is an example of a queue log with the error:
 
REQUEST#: 4269
Calling procedure: MSAPPPRC - Execute Segmentation on 10/10/2018 at 09:50:17
Parameter = Appeal Name Value = SBN 1811 DONATION
Parameter = Create Audience? Value = N
Parameter = Run Segmentation – Counts Only? Value = N
Parameter = Run Segmentation? Value = Y
Parameter = Export to Segmentation Matrix? Value = N
Parameter = Import Segmentation Matrix Selections? Value = N
Parameter = Delete Criteria after Import? Value = 
Parameter = Create Outputs? Value = N
Parameter = Generate SAC on Universe Table? Value = N
Parameter = Create Interactions? Value = N
Parameter = Set SAC on Interactions? Value = Y
Parameter = Update Account Activities? Value = Y
Parameter = Set Number of Solicitations? Value = Y
Executing SY_DDL command: begin appeal_pkg.execute_all(); end;
Running segmentation on 10/10/2018 at 09:50:19...
Script ended in error: ORA-20999: Procedure error: Criteria not valid, segment sequence: 24; ORA-00936: missing expression
ORA-06512: at "PBDS.DP_RAISE_GENERIC_ERROR", line 20
ORA-06512: at "PBDS.APPEAL_PKG", line 262
ORA-06512: at "PBDS.APPEAL_PKG", line 646
ORA-06512: at "PBDS.APPEAL_PKG", line 2805
ORA-06512: at "PBDS.APPEAL_PKG", line 2862
ORA-06512: at line 1
Reason #1
This error commonly occurs when the Ask Amount rules used on the Source for the Segments are not configured correctly. If they are missing an Activity Type and/or Base Table, then that would cause the error.  Correct the Ask Amount rules, or change the Ask Amount rule on the Source to another one that is setup correctly, then run the Appeal again.

Reason #2
The Segment Criteria was missing for a specific segment (in this case, "REM: All Other", which was SEGMENT_SEQ = 24 in the APPEAL_SEGMENTS table).  You can see this in the front-end, in Appeal Segmentation Entry, in the Segments tab, clicking on each Segment until you find one without any criteria (at the bottom of the dialog box):

User-added image

And in the back-end:
 
SELECT *
FROM   APPEAL_SEGMENTS AS1, APPEAL_SEGMENTS_CRITERIA ASC1
WHERE  UPPER(AS1.APPEAL_NAME) = UPPER( '[Appeal Name]' )     -- Take the Appeal name from the log
      AND AS1.SEGMENT_SEQ = 24    -- Take this from the error (ORA-20999: Procedure error: Criteria not valid, segment sequence: 24;)
      AND AS1.APPEAL_NAME = ASC1.APPEAL_NAME
      AND AS1.SEGMENT_SEQ = ASC1.SEGMENT_SEQ
ORDER  BY AS1.APPEAL_NAME, AS1.SEGMENT_SEQ;
 
To fix, add a proper criteria (i.e. Account_ID Is Not Null).

Environment

 RSR

Was this article helpful?