In this blog post ( https://zahirmohideen.blogspot.com/2024/04/oracle-23c-new-feature-grouping-by-alias.html ), we were introduced to a minor enhancement to the group by clause.
We can also use the column position in the group by clause in Oracle 23ai, if the parameter (group_by_position_enabled) is set to TRUE.
From my perspective, this shortcut makes the code short, but it may not be readable or ideal in the long term.
Again, this is my view.
Here is an example.
SQL> create table t as select object_name , object_type from all_objects;
Table T created.
SQL> sho parameter group_by_po
NAME TYPE VALUE
------------------------- ------- -----
group_by_position_enabled boolean FALSE
SQL> select object_type , count(*) as cnt from t group by 1 having cnt > 1;
Error starting at line : 1 in command -
select object_type , count(*) as cnt from t group by 1 having cnt > 1
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-03162: "OBJECT_TYPE": must appear in the GROUP BY clause or be used in an aggregate function as 'group_by_position_enabled' is FALSE
Help: https://docs.oracle.com/error-help/db/ora-03162/
More Details :
https://docs.oracle.com/error-help/db/ora-03162/
SQL> alter session set group_by_position_enabled = true;
Session altered.
SQL> select object_type , count(*) as cnt from t group by 1 having cnt > 1;
OBJECT_TYPE CNT
_____________________ ________
TABLE 2510
CLUSTER 10
INDEX 2486
SYNONYM 13105
TABLE PARTITION 656
INDEX PARTITION 608
SEQUENCE 37
DIRECTORY 12
PACKAGE 701
VIEW 8446
FUNCTION 307
PROCEDURE 54
TYPE 1656
CONTEXT 23
OPERATOR 60
DOMAIN 109
LOB 3
TABLE SUBPARTITION 33
LOCKDOWN PROFILE 3
PACKAGE BODY 165
CONSUMER GROUP 18
JOB CLASS 3
DESTINATION 2
SCHEDULE 4
WINDOW 9
SCHEDULER GROUP 4
EVALUATION CONTEXT 2
XML SCHEMA 19
INDEXTYPE 11
TYPE BODY 81
RULE SET 2
TRIGGER 129
JAVA CLASS 32754
JAVA RESOURCE 1228
JAVA DATA 17
LIBRARY 25
36 rows selected.
Let me know your comments, please!
