NULLS FIRST problem with numeric fields

From: Federico Albesano - Technical Design S.r.l. <federico.albesano_at_technicaldesign.it>
Date: Thu, 5 Aug 2004 15:44:20 +0200
Message-ID: <cetdcp$c39$1@grillo.cs.interbusiness.it>

Hi all,
I have Oracle 8.1.7 with a table:

CREATE TABLE PREDTABSTATOPRATICA
(

IDSTATOPRATICA NUMBER(5) NOT NULL,
STATOPRATICA VARCHAR2(100)
);
ALTER TABLE PREDTABSTATOPRATICA ADD ( CONSTRAINT PK_PREDTABSTATOPRATICA PRIMARY KEY ( IDSTATOPRATICA ) ) ; If I
ORDER BY STATOPRATICA NULLS FIRST
I get the correct result: first empty strings (null), then an alfabetically ascending ordered list.

If I
ORDER BY IDSTATOPRATICA NULLS FIRST
I get a list ordered in a numerical DESCENDING manner, even if I specify also ASC !!! :-(

Is this a bug? Is there an available patch? Is there a workaround?

I know in thisi case IDSTATOPRATICA can't be NULL from it's definition, but I have an automatic query translator (from MSSQL to ORACLE) so I can't distiguish string from numeric fields only from the query text, but I want empty strings to come first in ASC and last in DESC order like MSSQL !!

Thanks in advance for help!

Federico Albesano Received on Thu Aug 05 2004 - 08:44:20 CDT