Home

An ORDER BY direction should be specified explicitly

Description

    The rule "An ORDER BY direction should be specified explicitly" means that when using an ORDER BY clause in a PL/SQL statement, the direction of the ordering (ascending or descending) should be explicitly stated. This helps to ensure that the results are consistent and predictable. Without explicitly specifying the direction, the results may vary depending on the database engine being used.

Key Benefits

  • Ensures consistent results: An ORDER BY direction should be specified explicitly to ensure consistent results across different databases.
  • Prevents unexpected results: Without an explicit ORDER BY direction, the query result can be unpredictable and vary from one database to another.
  • Improves query performance: Specifying an ORDER BY direction can improve query performance by allowing the database to optimize the query execution plan.

 

Non-compliant Code Example

SELECT
    NAME,
    ADDRESS,
    CREDIT_LIMIT
FROM
    CUSTOMERS
ORDER BY CREDIT_LIMIT;  --Non compliant code (Order by clause without direction)

Compliant Code Example

SELECT
    NAME,
    ADDRESS,
    CREDIT_LIMIT
FROM
    CUSTOMERS
ORDER BY CREDIT_LIMIT ASC;  --Compliant code (Order by clause with direction)
Visual Expert 2024
 VEPLSQLRULE113