1. When testing the SQL query in Oracle SQL Developer or another query tool, connect to the database with user ACMDB. The RSA Identity Governance & Lifecycle report engine uses ACMDB for the database connection. To view the same information in the AVUSER schema, prefix the tables described in the RSA Identity Governance & Lifecycle Public Database Schema Reference with PV_. For example, table USER would be PV_USER.

2. When creating/editing the query in a Report Chart, because the report engine uses the ACMDB schema, to use tables in the AVUSER schema, they need to be both prefixed with PV_ and with the AVUSER username. For example, table USER would become AVUSER.PV_USER in the query.

3. Surround the entire SQL query with parenthesis and embed the query as a sub-select statement in the form (SELECT * FROM (your_query)). Sometimes only the outer parentheses are sufficient but the outer select allows for ORDER BY and other SQL clauses that may cause issues so it is a better practice to embed yoiur query in its own set of parentheses.

SELECT
    current_state   AS state,
    count(1)        AS TOTAL_COUNT
FROM
    change_request cr
WHERE
    cr.request_date_no_time >= trunc(current_date) - 30
GROUP BY
    current_state
ORDER BY current_state

(SELECT * FROM 
  (
  SELECT
    current_state   AS state,
    count(1)        AS TOTAL_COUNT
  FROM
    change_request cr
  WHERE
    cr.request_date_no_time >= trunc(current_date) - 30
  GROUP BY
    current_state
  ORDER BY current_state
  )
)