Support Article

Error on executing SQL if there is exact match request in Regex

SA-57246

Summary



Error occurs when executing the below SQL query in an RDB when there is an exact match request in the regular expression (regex).

SELECT 
pyid AS ".pyID", 
pystatuswork AS ".pyStatusWork", 
regexp_substr(pyid,'UNSC[A-Z]+') AS ".pyWorkIDPrefix", 
substr(regexp_substr(pyid,'LKYC[A-Z]{2}'),-2,2) AS ".pyCountry
FROM 
{Class: MY-Work
WHERE 
PYID like 'UNSC%' 
AND PYSTATUSWORK LIKE 'Pending%' 
AND PYCUSTOMER = nvl ({WorkPage.Entity(1).PartyID},'~')



Error Messages



The reference 2 is not valid. Reason: invalid property name: '2'


Steps to Reproduce



Replace the regex pattern from {2} to + as below,

substr(regexp_substr(pyid,'LKYC[A-Z]+'),-2,2) AS ".pyCountry"


Root Cause



A software use or operation error.
Not all SQL can be parsed.


Resolution



As a local-change, use the ASIS feature as below.

SELECT 
pyid AS ".pyID", 
pystatuswork AS ".pyStatusWork", 
regexp_substr(pyid,'UNSC[A-Z]+') AS ".pyWorkIDPrefix", 
{asis: "substr(regexp_substr(pyid,'LKYC[A-Z]{2}'),-2,2)" } AS ".pyCountry
FROM 
{Class: MY-Work
WHERE 
PYID like 'UNSC%' 
AND PYSTATUSWORK LIKE 'Pending%' 
AND PYCUSTOMER = nvl ({WorkPage.Entity(1).PartyID},'~')

Published May 9, 2018 — Updated May 10, 2019

Have a question? Get answers now.

Visit the Pega Support Community to ask questions, engage in discussions, and help others.