Re: All rows if null

On Apr 4, 5:49 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Apr 4, 4:51 am, banaslee <banas..._at_gmail.com> 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
>
> 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 || '%'
>
> >  ...     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
>
> HTH,
>   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 = ''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 :/ Received on Sat Apr 05 2008 - 08:26:08 CDT