ORA-00979: not a GROUP BY expression

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)

Learning:
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.

Reference:
https://docs.oracle.com/cd/E11882_01/network.112/e40393/redaction_guidelines.htm#ASOAG10532
(Loop up the contents under: 7.1 General Usage Guidelines)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s