Path: text.usenetserver.com!out04a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!n58g2000hsf.googlegroups.com!not-for-mail From: Ed Prochak Newsgroups: comp.databases.oracle.misc Subject: Re: All rows if null Date: Fri, 4 Apr 2008 10:49:23 -0700 (PDT) Organization: http://groups.google.com Lines: 45 Message-ID: <9f8d48ee-f80b-46e6-a363-85dae79eb3a3@n58g2000hsf.googlegroups.com> References: NNTP-Posting-Host: 206.54.145.254 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1207331363 17716 127.0.0.1 (4 Apr 2008 17:49:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 4 Apr 2008 17:49:23 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: n58g2000hsf.googlegroups.com; posting-host=206.54.145.254; posting-account=3ty6FAkAAACYEfch20jQ1ZACFatw-Vdx User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13,gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.misc:252356 X-Received-Date: Fri, 04 Apr 2008 12:49:23 EST (text.usenetserver.com) On Apr 4, 4:51 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, ' ') =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 '%' so 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 || '%' ? 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=E1bio Oliveira HTH, Ed