Re: NULLS FIRST problem with numeric fields

"Federico Albesano - Technical Design S.r.l." <federico.albesano_at_technicaldesign.it> a écrit dans le message de news:cetdcp$c39$1_at_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
>

Can you post your test case as it seems to work for me (8.1.7.4 WinNT4SP6):

SQL> create table t (id number, name varchar2(30));

Table created.

SQL> insert into t
  2 select decode(sign(dbms_random.value-0.8),1,to_number(null),rownum),   3 decode(sign(dbms_random.value-0.8),1,to_char(null),object_name)   4 from dba_objects
  5 where rownum <= 20
  6 order by dbms_random.value;

20 rows created.

SQL> commit;

Commit complete.

SQL> select * from t;

        ID NAME

---------- ------------------------------
        19
         2 ALL_ALL_TABLES
           ALL_COL_PRIVS_RECD
        10 ALL_COL_PRIVS
        17 ALL_DEF_AUDIT_OPTS
           ALL_COL_COMMENTS

           ALL_CONSTRAINTS
         3
         1 ACCESS$
         7 ALL_CLUSTER_HASH_EXPRESSIONS
         5 ALL_CATALOG
        18 ALL_DEPENDENCIES
           ALL_ASSOCIATIONS
        16 ALL_DB_LINKS
        15 ALL_CONTEXT
         8
        14
         6 ALL_CLUSTERS
        20

20 rows selected.

SQL> select * from t order by name nulls first;

        ID NAME

---------- ------------------------------
        19

         3
        20
        14
         8
         1 ACCESS$
         2 ALL_ALL_TABLES
           ALL_ASSOCIATIONS
         5 ALL_CATALOG
         6 ALL_CLUSTERS
         7 ALL_CLUSTER_HASH_EXPRESSIONS
           ALL_COL_COMMENTS
        10 ALL_COL_PRIVS
           ALL_COL_PRIVS_RECD
           ALL_CONSTRAINTS
        15 ALL_CONTEXT
        16 ALL_DB_LINKS
        17 ALL_DEF_AUDIT_OPTS
        18 ALL_DEPENDENCIES

20 rows selected.

SQL> select * from t order by id nulls first;

        ID NAME

---------- ------------------------------
           ALL_COL_PRIVS_RECD
           ALL_COL_COMMENTS
           ALL_CONSTRAINTS

           ALL_ASSOCIATIONS
         1 ACCESS$
         2 ALL_ALL_TABLES
         3
         5 ALL_CATALOG
         6 ALL_CLUSTERS
         7 ALL_CLUSTER_HASH_EXPRESSIONS
         8
        10 ALL_COL_PRIVS
        14
        15 ALL_CONTEXT
        16 ALL_DB_LINKS
        17 ALL_DEF_AUDIT_OPTS
        18 ALL_DEPENDENCIES
        19
        20

20 rows selected.

--
Regards
Michel Cadot