Re: SQL STATEMENT

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 27 Aug 1998 14:30:01 GMT
Message-ID: <35e56137.2730305@dcsun4.us.oracle.com>

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
>
>
>
>
Received on Thu Aug 27 1998 - 09:30:01 CDT