Re: All rows if null

From: Ed Prochak <>
Date: Fri, 4 Apr 2008 10:49:23 -0700 (PDT)
Message-ID: <>

On Apr 4, 4:51 am, 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

I'd be interested in seeing this work because I do not believe it. if is NULL the the Right hand side of the expression becomes just '%' so the expression becomes essentially'%'
which does NOT return all the rows

Perhaps you meant
WHERE nvl(, '%') = || '%'
 but that only returns rows where both and are null, so you must have meant
WHERE nvl(, ' ') LIKE || '%'

> ... 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?

use to_char() and your previous condition works.

> I hope I had been enough specific on my question.

Well it is not really clear to me even though I gave some remarks. You are building the query dynamically because...?

> Thanks in advance for your help,
> Fábio Oliveira

  Ed Received on Fri Apr 04 2008 - 12:49:23 CDT

Original text of this message