Sorting dates with NULL values

[Fry, Mark]

Hi Bernt,

I'm guessing you're doing the ordering by using an ORDERBY clause on the
access statement for the file, which is getting passed to the database as an
SQL 'order by'.

Unfortunately the SQL standard does not specify whether nulls get sorted
first or last, so different database vendors implement different behaviour.

To quote my esteemed colleague and SQL guru Diane Brown:

'The SQL standard says it is up to the vendor to decide whether nulls sort
first or last, so you should not get too attached to any particular
behaviour :-)

Quoting from the tome itself:
"Whether a sort key value that is null is considered greater or less than a
non-null value is implementation-defined, but all sort key values that are
null shall either be considered greater than all non-null values or be
considered less than all non-null values."'
<end Diane's quote>

>From your example, it appears that ORACLE sorts nulls as being greater than
non-null values.  I'm not aware of any direct method to force ORACLE to
behave differently, but you could try a pretty nasty SQL hack in your screen
to get it to sort with the nulls first.

The gist of it is this.  Bin your FILE statement for the primary file, and
replace it with a cursor declaration and a cursor statement.  The cursor
contains 2 selects, with the results glued together by UNION.  The first
select pulls out the rows with non-null dates, and converts the dates to
ASCII in a derived column.  The second select pulls out the rows with null
dates and includes a zero ASCII date in a corresponding derived column.  You
then sort on the contents of the derived column.

[Assume your table is called "fred" and your datetime column is called
"testdate"...]

SQL in <database> &
declare FRED cursor for &
SELECT foo, blat, testdate, &
  ascii(extract(year from testdate))||'-'||&
  ascii(extract(month from testdate))||'-'|| &
  ascii(extract(day from testdate)) as dummysort &
FROM fred &
WHERE testdate is not null &
UNION &
SELECT foo, blat, testdate, &
  ascii(0)||'-'||ascii(0)||'-'||ascii(0) &
FROM fred &
WHERE testdate is null

CURSOR FRED primary occurs 6 KEY foo
access via foo using foo request foo orderby dummysort
access sequential orderby dummysort

Drawbacks?  Well, the cursor will be read-only, and you might have to do a
bit more work on the derived column to get the sort order spot-on.  You
should probably take this idea as a starting point - perhaps investigate
setting up the cursor as a reference rather that a primary?

Good luck!

Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com



-----Original Message-----
From: bert.dn@arma.nl [mailto:bert.dn@arma.nl]
Sent: Tuesday, December 12, 2000 11:04 AM
To: powerh-l@sphere.swau.edu
Subject: Sorting dates with NULL values


Hi All,

We have a problem with the order of date fields with NULL values.
We are running PH820 with Oracle RDBMS with NULL value support.
In a cluster screen we would like to have a primary file ordered by a date
field in the following order:
  Null values first, existing dates ordered ascending
  but order by ascending or descending does not help us out



Ascending        Descending		What we want
----------------------------------------------------------
2000/12/01       NULL               NULL
2000/12/02       2000/12/31         2000/12/01
2000/12/31       2000/12/02         2000/12/02
NULL             2000/12/01         2000/12/31


Any ideas?


Kindest regards,
Arma bv



Bert de Nooij
Einsteinbaan 4
3439 NJ Nieuwegein
mailto:Bert.DN@Arma.com
http://www.arma.com

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.