Re: SQL STATEMENT

Thanks Chris,

   That helped immensely.  

On Thu, 27 Aug 1998 14:30:01 GMT, clbeck_at_us.oracle.com (Christopher Beck) wrote:

>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
>>approaches and some work well but not in every scenario. Since
>>there are three fields then there are 8 possible ways inputs can be
>>anticipated. I hope I haven't confused anyone. I used to do something
>>in Access like this...
>> ...where myfield = myinput or myfield is null;
>>
>>Oracle needs something else it would appear.
>>
>
>You can either
>
>1. Write the eight queries, NOT!
>
>
>2. Use the following query
>
>select foo, bar
> into localVariable1, localVariable2
> from T
> where ( myInput1 = col1 or myInput1 is null )
> and ( myInput2 = col2 or myInput2 is null )
> and ( myInput3 = col3 or myInput3 is null )
>
>
>3. Use dynamic SQL
>
>eg.
>
>create or replace
>procedure getZipCodes( myInput1 varchar2 default null,
> myInput2 varchar2 default null,
> myInput3 varchar2 default null ) is
> c number;
> q varchar2(32767);
> s number;
> localVariable1 varchar2(32767);
> localVariable2 varchar2(32767);
>begin
> q := 'select foo, bar from T where';
> if myInput1 is not null then
> q := q || ' col1 = ' || myInput1 || ' and';
> end if;
> if myInput2 is not null then
> q := q || ' col2 = ' || myInput2 || ' and';
> end if;
> if myInput3 is not null then
> q := q || ' col3 = ' || myInput3;
> end if;
> --
> -- remove the trailing ' where' if not inputs exist
> --
> q := rtrim( q, ' where' );
> --
> -- remove the trailing ' and'
> --
> q := rtrim( q, ' and' );
> --
> -- Execute the query and process rows
> --
> c := dbms_sql.open_cursor;
> dbms_sql.parse( c, q, dbms_sql.native );
> dbms_sql.define_column( c, 1, localVariable1, 32767);
> dbms_sql.define_column( c, 2, localVariable2, 32767);
> s := dbms_sql.execute( c );
> loop
> exit when dbms_sql.fetch_rows( c ) <= 0;
> dbms_sql.column_value( c, 1, localVariable1 );
> dbms_sql.column_value( c, 2, localVariable2 );
> --
> -- do something
> --
> end loop;
> dbms_sql.close_cursor( c );
>end getZipCodes;
>
>
>
>Hope this helps.
>
>chris.
>
>>Thanks
>>Brian
>>
>>
>>Good Luck! Brian_RestonVA
>>
>>
>>
>>

Good Luck! Brian_RestonVA Received on Fri Sep 04 1998 - 12:03:18 CDT