org.sonar.l10n.plsqlopen.rules.plsql.ToCharInOrderBy.html Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of zpa-checks Show documentation

Z PL/SQL Analyzer :: Checks

The newest version!

The usage of TO_CHAR function in a ORDER BY is almost always a mistake.
  The column value will be ordered alphabetically instead of being ordered according its datatype.

Noncompliant Code Example

Suppose we have the data below in the EMP table:


  
    empno
    hiredate
  
  
    1
    2019-10-01
  
  
    5
    2019-10-10
  
  
    15
    2018-10-02
  
  
    20
    2018-10-20
  


The queries below return an unwanted result:

select empno from emp order by to_char(empno);


  
    empno
  
  
    1
  
  
    15
  
  
    20
  
  
    5
  


select hiredate from emp order by to_char(hiredate, 'dd-mm-rrrr');


  
    hiredate
  
  
    01-OCT-19
  
  
    02-OCT-18
  
  
    10-OCT-19
  
  
    20-OCT-18
  


Compliant Code Example

To order these columns correctly we must remove the TO_CHAR call.

select empno from emp order by empno;


  
    empno
  
  
    1
  
  
    5
  
  
    15
  
  
    20
  


select hiredate from emp order by hiredate;


  
    hiredate
  
  
    02-OCT-18
  
  
    20-OCT-18
  
  
    01-OCT-19
  
  
    10-OCT-19