Oracle Database 23ai : Group By and Having using Column Aliases

Starting with Oracle Database 23ai, you can use column aliases directly in the GROUP BY and HAVING clauses. This functionality was not available in earlier versions of Oracle Database. Example in Oracle 21c: In Oracle 21c, attempting to use column aliases in the GROUP BY or HAVING clauses would result in an error: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB 2 FROM dba_segments 3 GROUP BY Malek 4 HAVING SIZE_MB > 100; ERROR at line 4: ORA-00904: "SIZE_MB": invalid identifier To avoid the invalid identifier error in Oracle 21c, you must rewrite the query without using the alias in the GROUP BY and HAVING clauses: SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB FROM dba_segments GROUP BY owner HAVING SUM(bytes / 1024 / 1024) > 100; Example in Oracle 23ai: In Oracle 23ai, both of the above queries are valid, and you can now use the alias SIZE_MB directly in the HAVING clause: Connected to Oracle Database 23ai Free, Release 23.0.0.0.0 SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB 2 FROM dba_segments 3 GROUP BY Malek 4 HAVING SIZE_MB > 100; MALEK SIZE_MB ------ ---------- sys 672 Additional Enhancement in Oracle 23ai: Oracle 23ai also introduces the ability to use column positions in the GROUP BY clause. To enable this feature, you need to set the group_by_position_enabled parameter to TRUE. Here’s how it works: SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB FROM dba_segments GROUP BY 1 HAVING SIZE_MB > 100; ERROR at line 1: ORA-00979: "OWNER": must appear in the GROUP BY clause or be used in an aggregate function After enabling the group_by_position_enabled parameter: SQL> ALTER SESSION SET group_by_position_enabled = TRUE; Session altered. SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB FROM dba_segments GROUP BY 1 HAVING SIZE_MB > 100; MALEK SIZE_MB ------ ---------- sys 672 Conclusion Oracle Database 23ai introduces significant improvements to SQL syntax, including the ability to use column aliases in GROUP BY and HAVING clauses and the use of column positions in GROUP BY. These enhancements simplify query writing and improve readability. Vahid Yousefzadeh Oracle Database Administrator Telegram channel :https://t.me/oracledb

Apr 25, 2025 - 16:49

Oracle Database 23ai : Group By and Having using Column Aliases

Starting with Oracle Database 23ai, you can use column aliases directly in the GROUP BY and HAVING clauses. This functionality was not available in earlier versions of Oracle Database.

Example in Oracle 21c:
In Oracle 21c, attempting to use column aliases in the GROUP BY or HAVING clauses would result in an error:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB
  2    FROM dba_segments
  3   GROUP BY Malek
  4  HAVING SIZE_MB > 100;
ERROR at line 4:
ORA-00904: "SIZE_MB": invalid identifier

To avoid the invalid identifier error in Oracle 21c, you must rewrite the query without using the alias in the GROUP BY and HAVING clauses:

SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB
  FROM dba_segments
 GROUP BY owner
HAVING SUM(bytes / 1024 / 1024) > 100;

Example in Oracle 23ai:
In Oracle 23ai, both of the above queries are valid, and you can now use the alias SIZE_MB directly in the HAVING clause:

Connected to Oracle Database 23ai Free, Release 23.0.0.0.0 
SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB
  2         FROM dba_segments
  3        GROUP BY Malek
  4       HAVING SIZE_MB > 100;

MALEK      SIZE_MB
------ ----------
sys         672

Additional Enhancement in Oracle 23ai:
Oracle 23ai also introduces the ability to use column positions in the GROUP BY clause. To enable this feature, you need to set the group_by_position_enabled parameter to TRUE. Here’s how it works:

SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB
  FROM dba_segments
 GROUP BY 1
HAVING SIZE_MB > 100;

ERROR at line 1:
ORA-00979: "OWNER": must appear in the GROUP BY clause or be used in an aggregate function

After enabling the group_by_position_enabled parameter:

SQL> ALTER SESSION SET group_by_position_enabled = TRUE;
Session altered.

SQL> SELECT LOWER(owner) AS Malek, SUM(bytes / 1024 / 1024) AS SIZE_MB
  FROM dba_segments
 GROUP BY 1
HAVING SIZE_MB > 100;

MALEK      SIZE_MB
------ ----------
sys         672

Conclusion
Oracle Database 23ai introduces significant improvements to SQL syntax, including the ability to use column aliases in GROUP BY and HAVING clauses and the use of column positions in GROUP BY. These enhancements simplify query writing and improve readability.

Vahid Yousefzadeh
Oracle Database Administrator
Telegram channel :https://t.me/oracledb