Re: All rows if null

From: DA Morgan <>
Date: Fri, 04 Apr 2008 09:31:50 -0700
Message-ID: <>

banaslee wrote:
> Hi there.
> I'm new to oracle and I'd want to build a form search.
> I'm currently using dynamic sql to append all the where clauses that
> has no null values on the corresponding form text boxes but I'm
> searching for a more elegant and static solution.
> In text I can use WHERE nvl(, ' ') = || '%' so that if
> is null it returns all the rows and I can still use an
> index if I create it like CREATE INDEX foo_idx ON a(nvl(foo, ' ')).
> But what about number values? Is there any better solution for them?
> I hope I had been enough specific on my question.
> Thanks in advance for your help,
> Fábio Oliveira

Use NVL or NVL2. For example:

WHERE NVL(mycol, 'ZZYZX') = or LIKE ...

Alternatively you can use SYS_OP_MAP_NONNULL but its use is not supported by Oracle.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Apr 04 2008 - 11:31:50 CDT

Original text of this message