Sorting dates with NULL values

[Walker, Chris]

1.  Try adding a column used for sorting.  If your Oracle database is Rdb
then you can use a computed (virtual) column.  IIRC Oracle Oracle doesn't
support computed columns, so you would have to have a real column maintained
by a trigger.

2.  Define a database view that implements a sort column (e.g.
coalesce(datefield, 31/12/9999) - decode does something similar in Oracle).
Depending on your database, you may be able to update the "real" columns in
this view.  If you can't, you could either use an Oracle update trigger or
update via a designer file.

Neither of these is a particularly elegant solution...

Chris Walker
Brainbench MVP for ASP
http://www.brainbench.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.