by DA Morgan <damorgan@[EMAIL PROTECTED]
>
Apr 4, 2008 at 09:31 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(a.foo, ' ') = b.foo || '%' so that if
> b.foo is null it returns all the a.foo 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 sup****ted by Oracle.
http://www.psoug.org/reference/undo***ented.html#uomn
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Wa****ngton
damorgan@[EMAIL PROTECTED]
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
