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

By -

 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

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Boolean Data Type in Oracle ِDatabase 23ai

By -
Image
  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...
Read more