On Apr 4, 5:49=A0pm, Ed Prochak <edproc...@[EMAIL PROTECTED]
> wrote:
> 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 '%' =A0so 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 || '%'
> ?
> =A0but that only returns rows where both a.foo and b.foo are null, so
> you must have meant
> WHERE nvl(a.foo, ' ') =A0LIKE b.foo || '%'
>
> > =A0... =A0 =A0 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,
> =A0 Ed
Yes, you're right, what I meant was WHERE nvl(a.foo, ' ') LIKE b.foo
|| '%'. Sorry about that :P
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 =3D ''A''' || where_clauses
Where where_clauses can be 'AND name LIKE ''John%''' or 'AND id_number
=3D 12345' or 'AND name LIKE ''John%'' AND id_number =3D 12345' or even
more possible arrangements (with DOB too of course).
I don't know another way of doing this :/
|
