📋Steps in this guide1/5

GROUP BY and HAVING Clauses Using Column Aliases in Oracle Database 23ai/26ai

From Oracle 23ai/26ai onward we can use the column alias in GROUP BY and HAVING clauses, or the column position in the GROUP BY clause.

oracle 23configurationintermediate

by OracleDba

16 views

Setup

The examples below use the following tables.

Code/Command (click line numbers to comment):

drop table if exists emp purge;
drop table if exists dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

insert into dept values
  (10,'ACCOUNTING','NEW YORK'),
  (20,'RESEARCH','DALLAS'),
  (30,'SALES','CHICAGO'),
  (40,'OPERATIONS','BOSTON');

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20),
       (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30),
       (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30),
       (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20),
       (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30),
       (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30),
       (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10),
       (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20),
       (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10),
       (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30),
       (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20),
       (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30),
       (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20),
       (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

The Problem

In previous releases of the database we were forced to repeat full references to the column definitions in the and clauses. In the following example we want to present a formatted version of the department name, and get a count of the employees for those departments with more than 3 employees. Notice we need to repeat the "initcap(d.dname)" reference in the clause and the "count(*)" reference in the clause. In this case both column references are quite small, but in some cases like analytic functions and case expressions they could be very large, making this repetition quite unwieldy.

Code/Command (click line numbers to comment):

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by initcap(d.dname)
having count(*) > 3;

DEPARTMENT         AMOUNT
-------------- ----------
Research                5
Sales                   6

SQL>

The Solution : Column Alias

From Oracle 23ai/26ai onward we can make use of the column aliases in the and clauses. In this example, rather than repeating the "initcap(d.dname)" reference in the clause, we use the column alias of "department". Likewise, instead of repeating the "count(*)" reference in the clause, we use the column alias of "amount". We no longer need to repeat complex column definitions in our and clauses.

Code/Command (click line numbers to comment):

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by department
having amount > 3;

DEPARTMENT         AMOUNT
-------------- ----------
Research                5
Sales                   6

SQL>

The Solution : Column Position

From Oracle 23ai/26ai onward we can make use of the column position in the clause. This functionality is not enabled by default, so it must be enabled at the session or system level. Here we enable it for our session. In this example, rather than repeating the "initcap(d.dname)" reference in the clause, we use the column position of "1". We are not able to use the column position in the clause for obvious reasons, as the database wouldn't know what was a column position and what was a number literal. Personally I would avoid this and use the column alias in preference.

Code/Command (click line numbers to comment):

alter session set group_by_position_enabled=true;

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by 1
having amount > 3;

DEPARTMENT         AMOUNT
-------------- ----------
Research                5
Sales                   6

SQL>

Query Transformation

Let's see what happens behind the scenes when we use this new syntax. First we flush the shared pool and identify the trace file that will be created for our new session. Now we do a 10053 trace of the statement. We check the resulting trace file, searching for the section beginning with "Final query after transformations", and we see the following statement. I've reformatted it a little for readability. The statement has been transformed in the following ways. - The alias in the clause has been replaced by the full reference to "initicap(dname)". - The alias in the clause has been replaced by the full reference to "count(*)". - The ANSI join syntax has been replaced by the old-style Oracle join syntax. This is has always been the case for ANSI joins. So allowing us to reference columns using aliases in the and clauses is syntactic sugar . We are able to write shorter and clearer SQL, and the optimizer converts it into the longer original syntax for us. For more information see: - SELECT - SQL for Beginners (Part 7) : The GROUP BY Clause and HAVING Clause - GROUP BY ALL in Oracle Database 23ai/26ai Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba
alter system flush shared_pool;


conn testuser1/testuser1@//localhost:1521/freepdb1

set linesize 100
column value format a65

select value
from   v$diag_info
where  name = 'Default Trace File';

VALUE
-----------------------------------------------------------------
/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_23256.trc

SQL>

alter session set events '10053 trace name context forever';

select initcap(d.dname) as department, count(*) as amount
from   dept d
join   emp e on d.deptno = e.deptno
group by department
having amount > 3;

alter session set events '10053 trace name context off';

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT INITCAP("D"."DNAME") "DEPARTMENT",
       COUNT(*) "AMOUNT"
FROM   "TESTUSER1"."DEPT" "D",
       "TESTUSER1"."EMP" "E"
WHERE  "D"."DEPTNO"="E"."DEPTNO"
GROUP BY INITCAP("D"."DNAME")
HAVING COUNT(*)>3

Comments (0)

Please to add comments

No comments yet. Be the first to comment!