ORA-00920: invalid relational operator while clicking a report on the UI of RSA Identity Governance & Lifecycle
Originally Published: 2018-12-06
Article Number
Applies To
RSA Product Set: RSA Identity Governance & Lifecycle 7.0.2 P10 HF01
Issue
Issue only applies to RSA Identity Governance & Lifecycle 7.0.2 P10 HF01.
And only if you are using 'TO_DATE' function in SQL.
When you click on a Report Name on 'Tabular Reports' page, it presents "Insufficient privileges to view this page"
And only if you are using 'TO_DATE' function in SQL.
When you click on a Report Name on 'Tabular Reports' page, it presents "Insufficient privileges to view this page"
This also affects the export function of the application.
You get "Request could not be handled" pop up on UI when you click on: Admin->Import/Export->Export:
From application logs:
ERROR (MSC service thread 1-6) [com.aveksa.server.report.Report] get all display cols
com.aveksa.server.runtime.ServerException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
Cause
As part of another fix (to try to fix an issue with dates not returning data properly in report as a column), application was adding ' as d' suffix to the to_date() functions , however it also did this on the 'where' clause causing the failure as it changed it to basically changed:
'WHERE ... TO_DATE(TMEU.CREATION_DATE, 'DD-MON-YY') = TO_DATE(SYSDATE, 'DD-MON-YY')-5)'
to
'WHERE ... TO_DATE(TMEU.CREATION_DATE, 'DD-MON-YY') as d = TO_DATE(SYSDATE, 'DD-MON-YY')-5)'
and causing the above error.
Resolution
It is fixed as part of RSA Identity Governance & Lifecycle 7.0.2 P10 HF02.
