Re: SQL STATEMENT

From: Keith Boulton <boulke_at_globalnet.co.nospamuk>
Date: Fri, 28 Aug 1998 08:03:54 GMT
Message-ID: <35e66063.1268173@news.globalnet.co.uk>

On Thu, 27 Aug 1998 01:38:04 GMT, beverett_at_remove_this.usa.net (Brian Everett) wrote:

>
> Greetings All.
>
> I have a table of zip codes and a web form that attempts to query
>the table based on three fields on the form. The user can provide as
>little or as much information in these three fields as they would
>like. Problem: I do not know in advance which fields will be entered.
>How do I structure the select statement (cursor (Oracle)) so that
>fields not entered will not restrict the returned records but field
>which are entered will restrict the output. I've tried a number of

The following should work, so long as none of the columns in the database table are allowed to be null.
select ...
from table t
where t.field1 = nvl( input1, t.field1) and t.field2 = nvl( input2, t.field2)
and t..field3 = nvl( input3, t.field3)

I'm not sure if this will use indexes on these fields or not. Alternative is to dynamically build the sql statement. Received on Fri Aug 28 1998 - 03:03:54 CDT