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

banaslee wrote:
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.
http://www.psoug.org/reference/undocumented.html#uomn
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

banaslee wrote:
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.
http://www.psoug.org/reference/undocumented.html#uomn
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

banaslee wrote:
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.
http://www.psoug.org/reference/undocumented.html#uomn
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

banaslee wrote:
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.
http://www.psoug.org/reference/undocumented.html#uomn
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote:
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='%'
which does NOT return all the a.foo rows

Perhaps you meant
WHERE nvl(a.foo, '%') = 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 || '%'


use to_char() and your previous condition works.

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

HTH,
Ed

On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote:
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='%'
which does NOT return all the a.foo rows

Perhaps you meant
WHERE nvl(a.foo, '%') = 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 || '%'


use to_char() and your previous condition works.

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

HTH,
Ed

On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote:
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='%'
which does NOT return all the a.foo rows

Perhaps you meant
WHERE nvl(a.foo, '%') = 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 || '%'


use to_char() and your previous condition works.

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

HTH,
Ed

On Apr 4, 4:51 am, banaslee <banas... (AT) gmail (DOT) com> wrote:
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='%'
which does NOT return all the a.foo rows

Perhaps you meant
WHERE nvl(a.foo, '%') = 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 || '%'


use to_char() and your previous condition works.

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

HTH,
Ed

On Apr 4, 5:49*pm, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Yes, you're right, what I meant was WHERE nvl(a.foo, ' ') LIKE b.foo
The to_char suggestion will work as I desire but I'm afraid that it
can't be as fast as numeric comparision even if I can build a function
index.

Why am I building the query dynamically? I have a search tool based on
a form with some text boxes, one for name, other for the date of birth
and another for ID number. Then, based on the values entered I want to
search for persons that match those criteria.
Imagine, if I have 'John' on name and all the other boxes empty then I
only want to see people with his name starting by John no matter his
ID number or his DOB.
This is why I use dynamic SQL:
'select * from people where status = ''A''' || where_clauses
Where where_clauses can be 'AND name LIKE ''John%''' or 'AND id_number
= 12345' or 'AND name LIKE ''John%'' AND id_number = 12345' or even
more possible arrangements (with DOB too of course).

I don't know another way of doing this :/