ORA-00920: invalid relational operator while clicking a report on the UI of RSA Identity Governance & Lifecycle

[#1]

Article Number

000036990

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"
 

Image description

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:

Image description

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.