On Apr 4, 4:51 am, banaslee <banas...@[EMAIL PROTECTED]
> 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(a.foo, ' ') =3D b.foo || '%' so that if
> b.foo is null it returns all the a.foo rows
I'd be interested in seeing this work because I do not believe it.
if b.foo is NULL the the Right hand side of the expression becomes
just '%' so the expression becomes essentially
a.foo=3D'%'
which does NOT return all the a.foo rows
Perhaps you meant
WHERE nvl(a.foo, '%') =3D b.foo || '%'
?
but that only returns rows where both a.foo and b.foo are null, so
you must have meant
WHERE nvl(a.foo, ' ') LIKE b.foo || '%'
> ... 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=E1bio Oliveira
HTH,
Ed
|
