While working on a Critical Issue, it comes a scenario wherein following exception is being obtained “ORA-00979: not a GROUP BY expression” in Oracle
After doing an analysis it has been observed that a redacted column cannot be used in a SQL statement where such a column has a function/expression applied and is used in a group by clause. If an action is performed then error “ORA-00979: not a GROUP BY expression” will be returned.
Sample example that works
SELECT COLUMN_NAME_1 FROM TABLE_NAME WHERE COLUMN_NAME_ID BETWEEN 500 AND 600 GROUP BY COLUMN_NAME_1
Sample example that doesn’t works
SELECT SUBSTR(COLUMN_NAME_1, 1, 5) FROM TABLE_NAME WHERE COLUMN_NAME_ID BETWEEN 500 AND 600 GROUP BY SUBSTR(COLUMN_NAME_1, 1, 5)
Do not include any redacted columns in a SQL expression that is used in a GROUP BY clause in a SQL statement. Oracle does not support this behavior, and raises an ORA-00979: not a GROUP BY expression error. This happens because internally the expression in the SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the GROUP BY clause (which is currently not updated by Data Redaction) leading to this unintended error message.
(Loop up the contents under: 7.1 General Usage Guidelines)