Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: NULL VALUES & Sort Order

Re: NULL VALUES & Sort Order

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Tue, 28 Jan 2003 08:39:30 -0800
Message-ID: <3E36B241.692DAFC@exesolutions.com>


beutler wrote:

> I've problems retrieving a table where the key fields may contain NULL
> VALUES. The first problem is, that Oracle does not seem to distinguish
> between NULL VALUES, Empty Strings or even blank strings (which are
> trimmed to NULL). Isn't there any way to change this behaviour ?
> I want to retrieve the data in logical order, that means for me empty
> fields first. Because Oracle trims empty strings or strings of blanks
> to NULL and by default, returns NULLs last, I tried to use the NULLS
> FIRST clause in my query. But this is very slow, it seams Oracle will
> then no longer use the index if the query constraints via the index
> fields...
> Isn't there any way to retrieve data with empty values first while
> reading using an index ?

Impossible in Oracle. You can not have a NULL primary key.

And Oracle definitely does treat NULLs as NULLs.

Rather than ranting about what you've not learned about Oracle go to http://tahiti.oracle.com and study Oracle concepts and architecture.

Until then try

SELECT field
FROM table
ORDER BY field NULLS FIRST;

Daniel Morgan Received on Tue Jan 28 2003 - 10:39:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US