How to control the sort order for NULL values with the DataServer?

Apr 18, 2017
Knowledge

Title

URL Name

How-to-control-the-sort-order-for-NULL-values-with-the-DataServer

Article Number

000159496

Environment

Product: OpenEdge Version: 10.x and later OS: All supported platforms Other: Oracle RDBMS, MS SQL RDBMS

Question/Problem Description

How to control the sort order for NULL values with the OpenEdge DataServer for Oracle?
How to control the sort order for NULL values with the OpenEdge DataServer for MS SQL Server?

Is there anything (configuration/tricks) that can be done to control the order of NULL values in a result set with Oracle or MS SQL Server?

Steps to Reproduce

Clarifying Information

On MS SQL Server, the ORDER BY ... ASC clause will always place NULL values first.
On MS SQL Server, the ORDER BY ... DESC clause will always place NULL values last.

On Oracle, the ORDER BY ... ASC clause will always place NULL values last.
On Oracle, the ORDER BY ... DESC clause will always place NULL values first.

The SQL standard does not explicitly define a default sort order for Nulls. Instead, on conforming systems, Nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. This is the case for Oracle RDBMS.
MS SQL Server does not implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.

Error Message

Defect Number

Enhancement Number

Cause

Resolution

Oracle

Use NULLS FIRST or NULLS LAST with the SEND-SQL-STATEMENT.
If the null ordering is not specified then the handling of the null values is:
NULLS LAST if the sort is ASC
NULLS FIRST if the sort is DESC
If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST.

MS SQL Server

The below SQL statement will return NULL values last, using MS SQL Server:

SELECT *
FROM Employees
ORDER BY (CASE
WHEN DepartmentId IS NULL THEN 1
ELSE 0
END),
DepartmentId

You will need to run the query using the SEND-SQL-STATEMENT, since there is no ABL construct that translates to the following statement:

CASE
WHEN <column> IS NULL THEN 1
ELSE 0
END

Workaround

Notes

References to other documentation:

"ORDER BY Clause." Oracle Database Documentation. Oracle®, n.d. Web. 18 Apr. 2017. <http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html>.

"Null Values." Microsoft MSDN. Microsoft®, n.d. Web. 18 Apr. 2017. <https://msdn.microsoft.com/en-us/library/ms191504.aspx>.

Olsen, Nick. "SQL: Order By with NULL Values Last." Nick Olsen's Programming Tips. N.p., 30 Sept. 2010. Web. 18 Apr. 2017. <https://nickstips.wordpress.com/2010/09/30/sql-order-by-with-null-values-last/>.

Keyword Phrase

Last Modified Date

4/18/2017 9:07 AM