Re: Order By not correct when using data returned by NVL statement

In article <8UvZ5.42359$R77.3445408_at_nnrp4.clara.net>,   "Andrew Emanuel" <freeside_at_clara.net> wrote:
> I have a query which selects three fields, one of which (a date) has
 an NVL
> statement to default the value to a specified date if null. The
 problem
> that I have is that the results of the query are ordered by the date
 with
> the NVL statement, but the result is displayed as though the NVL
 statement
> was not used.
>
> The rows returned are ordered by the date where the date actually
 contains a
> value, but the rows where the date is null are placed at the top even
 though
> the value that is given to the date should appear lower down (that is
 they
> are treated as if the date is still null).
>
> This query worked correctly under SQL 7.3.4. The problem occurs with
 SQL
> 8.1.5.
>
> I have placed the NVL statement in the order by clause to no avail.
>
> Any ideas?
>
>

change your order by:

             order by .. nvl( datefield, '01-jan-00')..

OR

             order by .. n .. where n is the column with the nvl date field

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com
http://www.deja.com/