ORACLE 23-C NEW FEATURES
Using column alias name in GROUP BY & HAVING clause
Using column position in GROUP BY & HAVING clause
CONTENTS
| SNO | CONTENTS | PAGE NUMBER |
| 1 | Introduction | 3 |
| 2 | Why do we need this feature? | 3 |
| 3 | Using column alias name in GROUP BY and HAVING clause | 4 |
| 4 | Column name position in GROUP BY Clause | 5 |
| 5 | Conclusion | 6 |
- Introduction
As new features within Oracle Database 23c, we can use aliases names and/or position number within the clause GROUP BY.
Column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses shall be used in SELECT statement.
Additionally, the HAVING clause supports column aliases.
- Why do we need this feature?
In previous releases of the database, we were forced to repeat full references to the column definitions in the GROUP BY and HAVING clauses.
Below screen shot refers that alias name cannot be used directly
The way SQL query is parsed and executed in Oracle is as follows:
FROM and JOIN clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause.
DISTINCT
ORDER BY clause
An alias introduced in step 5 cannot be yet referenced in step 3.
Grouping normally works only when the respective expression in the GROUP BY clause is repeated. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.
Correct Method:
- Using column alias name in GROUP BY and HAVING clause
column aliases in the GROUP BY and HAVING clauses in the SELECT statement.
The statement has been transformed in the following ways.
The alias in the GROUP BY clause has been replaced by the full reference.
The alias in the HAVING clause has been replaced by the full reference.
Benefits
Allowing to reference columns using aliases in the GROUP BY and HAVING clauses is syntax candy.
Able to write shorter and clearer SQL. It can make SQL queries much more readable and maintainable while providing better SQL code portability.
the optimizer converts it into the longer original syntax.
- Column name position in GROUP BY Clause
This feature allows to use column positions in the GROUP BY clause instead of repeating the entire expression from the SELECT list. This can make queries easier to read and write.
Enabling the feature
Before using position number within group by clause, we need to enable this feature within the session Level/ System level:
Session level:
ALTER SESSION statement to specify or modify any of the conditions or parameters that affect the connection to the database. The statement stays in effect until the database is disconnected.
System Level:
The ALTER SYSTEM command permanently changes a system setting. The new setting persists across all sessions.
Session level settings override system level settings.
Conclusion
Oracle 23C new feature Group by alias name / Group by position are very much useful to make the query readable
