org.sonar.l10n.plsqlopen.rules.plsql.NotASelectedExpression.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!


In a SELECT DISTINCT with an ORDER BY clause, if you specify a value in the ORDER BY that does not exists 
in the SELECT clause, Oracle will raise the exception ORA-01791: not a SELECTed expression. 


See this example:

SELECT DISTINCT item.name
  FROM item
 ORDER BY item.group_id



The item.id column is not in the SELECT clause, so Oracle will raise an ORA-01791. The corrected version can be: 


SELECT DISTINCT item.name, item.group_id
  FROM item
 ORDER BY item.group_id



If the column in SELECT clause has an alias, you also can use the alias in the ORDER BY clause:


-- valid queries
SELECT DISTINCT item.name AS full_name
  FROM item
 ORDER BY item.name;
 
SELECT DISTINCT item.name AS full_name
  FROM item
 ORDER BY full_name;



Be aware that until the version 11.2.0.4, Oracle accepted some incorrect values in ORDER BY, like:


SELECT DISTINCT UPPER(item.name) AS full_name, item.group_id
  FROM item
 ORDER BY item.name -- should be "UPPER(item.name)" or "full_name" 



You should fix the queries to avoid compatibility issues with newer versions of the Oracle database.