G-3183

🆓

Warning

Always specify column aliases instead of expressions in GROUP BY clauses.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 5 mins
Quick Fix | Local-medium
Database Support: OracleDB (v23.3.0 +)

Reason

Starting with Oracle Database 23c, it is possible to use a column alias in the group by clause instead of repeating the expression used in the select list.

Unless you use rollup, cube or grouping sets, it is not necessary to use expressions in the group by clause.

Example

Non-Compliant Example

select lower(job_id) as job
      ,sum(salary) as sum_salary
  from employees
 group by lower(job_id)
 order by job;

Issues

Line	Column	Message
4	11	Use column alias 'job' in GROUP BY clause.

Compliant Solution - ★★★★★

select lower(job_id) as job
      ,sum(salary) as sum_salary
  from employees
 group by job
 order by job;

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
DisableAllQuickFix	Comma-separated ist of rules for which a quick fix should not be applied to all the problems in a file.	Core G-3130

References

same as Trivadis G-3183