Re: All rows if null
Date: Sat, 5 Apr 2008 06:26:08 -0700 (PDT)
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
> 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
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