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 10, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.