What’s New in Oracle Database 23c - Group By Alias - Demonstration

August 28, 2023  

 What’s New in Oracle Database 23c - Group By Alias - Demonstration!

• Oracle Database 23c delivers the most complete and simple converged database for developers looking to build new microservice, graph, document, and relational applications.
• Since Oracle's 23C is a long term release, the database has already been released for developers on April 6th, 2023. Oracle Cloud's non-production environments are updated on the first Friday of your quarterly update month.
• Whether you are a Developer, a Data Scientist, a DBA, an Educator, or just interested in databases, Oracle Database 23c Free—Developer Release is the ideal way to get started.
• Oracle will share new features updates about Oracle Database 23c with examples so you can get up and running quickly. 

Okay, Let’s See the Feature Highlights…

GROUP BY column alias:

You can now use column alias or SELECT item position in GROUP BY, GROUP BY CUBE, GROUP BY ROLLUP, and GROUP BY GROUPING SETS clauses. Additionally, the HAVING clause supports column aliases. These new Database 23c enhancements make it easier to write GROUP BY and HAVING clauses, making SQL queries much more readable and maintainable while providing better SQL code portability.

In previous versions of Oracle, I could never understand why a GROUP BY clause in a SELECT statement does not allow a column alias or a position number. For ORDER BY this is allowed, but not for GROUP BY. For example, if you run the following query on an Oracle 19c database, it will cause an error message:

Demo:

In Oracle 19c Database:

SQL> create table pepsi_delivery (year number, pepsi varchar2(10), pepsi_type varchar2(10));

Table created.

SQL> insert into pepsi_delivery values (2020, '500 ml', 'Cane');

1 row created.

SQL> insert into pepsi_delivery values (2021, '300 ml', 'Cane');

1 row created.

SQL> insert into pepsi_delivery values (2022, '200 ml', 'Bottle');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT TO_CHAR(bottling_date, ‘YYYY’) year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY year, pepsi, pepsi_type ORDER BY 1, 2, 3;

SELECT TO_CHAR(bottling_date, ‘YYYY’) year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY year, pepsi, pepsi_type ORDER BY 1, 2, 3

                              *

ERROR at line 1:

ORA-00904: "???YYYY???": invalid identifier

The problem is the column alias year that is used in the SELECT clause. An alias is allowed in the ORDER BY clause, but not in the GROUP BY. There is no logical restriction for this – other relational databases support this syntax since years. In Oracle, we had to copy the expression of the SELECT clause into the GROUP BY clause to run the SQL statement:

SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

GROUP BY Column Alias or Position in Oracle 23c:

With Oracle 23c, this restriction does not exist anymore. Now, it is possible to use a column alias or the column position in a GROUP BY – in the same way as we know it from the ORDER BY clause. This was one of the first features I tested on the new 23c release, and it worked as expected for the column alias. 

But for the positional notation, I got another error message:

SQL> SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

Error starting at line : 1 in command -

SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3

Error at Command Line : 1 Column : 8

Error report -

SQL Error: ORA-00979: "YEAR": must appear in the GROUP BY clause or be used in an aggregate

function

00979. 00000 -  "not a GROUP BY expression"

*Cause:    

*Action:

SQL> 

To avoid this error, the parameter group_by_position_enabled must be set to TRUE (it is FALSE by default).

SQL> show parameter group_by

NAME                      TYPE    VALUE 

------------------------- ------- ----- 

group_by_position_enabled boolean FALSE 

SQL> 

SQL> ALTER SESSION SET group_by_position_enabled=TRUE;

Session altered.

SQL> show parameter group_by

NAME                      TYPE    VALUE 

------------------------- ------- ----- 

group_by_position_enabled boolean TRUE  

SQL> SELECT year, pepsi, pepsi_type FROM pepsi_delivery GROUP BY 1, 2, 3 ORDER BY 1, 2, 3;

   YEAR PEPSI     PEPSI_TYPE    

_______ _________ _____________ 

   2020 500 ml    Cane          

   2021 300 ml    Cane          

   2022 200 ml    Bottle   

After that, you can use column names, column aliases or positions in the GROUP BY part of your queries. This is a nice little extension, but it can be useful to reduce the complexity of complex queries.

This is just one of many new features that were introduced with Oracle 23c. If you didn’t do yet, download and install the Oracle Database 23c Free – Developer Release. There you can play around with many other extensions of the new release. An overview of all new features you can find in the Oracle Database New Features manual of the Oracle 23c documentation. Also I am going to explain each and every new features in upcoming videos.

References:

https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/introduction.html#GUID-C852CD78-8F16-4449-8BE0-D85C80D38E3C

https://www.oracle.com/database/23c

Cheers!

Ramesh

Connect me on:

Blog: https://www.oralgwr.com/

LinkedIn: https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69/

Twitter: https://twitter.com/sachinrameshdba

Facebook: https://www.facebook.com/rameshkumar.krishnamoorthy.9/

Facebook Page: https://www.facebook.com/oraclef1

Instagram: https://www.instagram.com/oraclef1_1

YouTube: https://www.youtube.com/oraclef1

YouTube: https://www.youtube.com/oracledbatutorialtamil