Troubleshooting
Problem
Alternative for NULLS [FIRST / LAST]
Symptom
Netezza does not presently have support for NULLS [FIRST / LAST]. For example:
]TEST(ADMIN)=> SELECT col1 FROM mytable ORDER BY col1 DESC nulls FIRST; ERROR: NULLs cannot specified to be placed first in the ORDER part of a SELECT statement in Netezza
TEST(ADMIN)=> SELECT col1 FROM mytable ORDER BY col1 DESC nulls LAST; ERROR: NULLs cannot specified to be placed last in the ORDER part of a SELECT statement in Netezza
[
Resolving The Problem
This document demonstrates an ANSI compliant alternative syntax for "NULLS [FIRST / LAST]" used to specify ordering of NULLs within ORDER BY clauses.
An alternative ANSI compliant syntax is available which provides for NULL ordering specification. Netezza supports this alternative convention within ordinary ORDER BY clauses as well as within the SQL-99 window partition specifications:
SELECT col1 FROM mytable ORDER BY col1 DESC nulls FIRST;
Becomes:
SELECT col1 FROM mytable ORDER BY col1 IS NULL DESC, col1 DESC;
COL1
------
6
2
1
(5 rows)
SELECT col1 FROM mytable ORDER BY col1 DESC nulls LAST;
Becomes:
SELECT col1 FROM mytable ORDER BY col1 IS NULL ASC, col1 DESC;
Or
SELECT col1 FROM mytable ORDER BY col1 DESC;
COL1
------
6
2
1
(5 rows)
SELECT col1 FROM mytable ORDER BY col1 ASC nulls FIRST;
Becomes:
SELECT col1 FROM mytable ORDER BY col1 IS NULL DESC, col1 ASC;
Or
SELECT col1 FROM mytable ORDER BY col1 ASC;
COL1
------
1
2
6
(5 rows)
SELECT col1 FROM mytable ORDER BY col1 ASC nulls last;
Becomes:
SELECT col1 FROM mytable ORDER BY col1 IS NULL ASC, col1 ASC;
COL1
------
1
2
6
(5 rows)
Historical Number
NZ935472
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21571638