E-QR: PS Query With Group Criteria Throws The Error "Illegal usage of parenthesis in criteria!" After PeopleTools 8.55 Upgrade (Doc ID 2278442.1)

Last updated on OCTOBER 16, 2018

Applies to:

Symptoms

When attempting to create a PS Query which uses "OR" Criteria and group the criteria using parenthesis, the following error occurs:

Error
-------
"Illegal usage of parenthesis in criteria!"


Steps
--------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Construct a query which contains criteria like A.MESSAGE_SET_NBR >= :1 AND A.MESSAGE_SET_NBR <= :2 which both prompts are optional prompts

Sample SQL from Query Manager:

SELECT A.MESSAGE_SET_NBR, A.MSG_SEVERITY, Count( A.MESSAGE_NBR)
FROM PSMSGCATDEFN A
WHERE ( ( A.MESSAGE_SET_NBR < :1
OR A.MESSAGE_SET_NBR = :1)
AND ( A.MESSAGE_SET_NBR > :2
OR A.MESSAGE_SET_NBR = :2))
GROUP BY A.MESSAGE_SET_NBR, A.MSG_SEVERITY
ORDER BY 1, 2, 3

Getting "Illegal usage of parenthesis in criteria! (30,12)" error on running the query (hit enter without entering/changing values when prompt for runtime prompts). Since both prompts are optional, they should be dropped from
criteria.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.