Re: Sql Query

  • From: Sanjay Mishra <smishra_97@xxxxxxxxx>
  • To: Thomas Day <tomday2@xxxxxxxxx>
  • Date: Mon, 20 Mar 2006 10:14:21 -0800 (PST)

Thomas
   
  I tried the following and it worked
  CREATE OR REPLACE procedure gen_proc is
  arg varchar2(500);
  col varchar2(100);
  p_col varchar2(100) := 'test';
  val varchar2(100);
  where_con varchar2(500);
  outs varchar2(500) := 'select c1 from some_table where ';
  begin
  -- PARAMETER
  arg:='c1,v1,c2,v2,c3,v3,c3,v3%,c3,v4';
   
   
  arg:=arg||',';
  loop
  col := substr ( arg,1, instr(arg,',')-1);
  arg:=substr ( arg, instr(arg,',')+1);
  val:=substr ( arg,1, instr(arg,',')-1);
  arg:=substr ( arg, instr(arg,',')+1);
  if ( p_col != col ) then
  
  if ( where_con is not null ) then
  where_con := where_con || ') and ( ';
  else
  where_con := where_con || '(';
  end if;
  
  if ( instr(val,'%')=0 ) then 
  where_con := where_con || ' ' || col || ' = ''' || val || '''' ;
  else
  where_con := where_con || ' ' || col || ' like ''' || val || '''' ;
  end if;
  
  else -- if col is repeated
  if ( instr(val,'%')=0 ) then 
  where_con := where_con || ' or ' || col || ' = ''' || val || '''';
  else
  where_con := where_con || ' or ' || col || ' like ''' || val || '''' ;
  end if; 
  end if; 
  
  if ( arg is null ) then
  exit;
  end if;
  p_col:=col;
  end loop;
   
  outs := outs || where_con || ')';
  dbms_output.put_line(outs );
  end;
  /
   
  Sanjay
  

Thomas Day <tomday2@xxxxxxxxx> wrote:
  I'm no expert but this sounds like a job for dynamic SQL - or else some 
programmatic solution.  Good luck and let us know what you find in the manual. 

                
---------------------------------
Yahoo! Mail
Bring photos to life! New PhotoMail  makes sharing a breeze. 

Other related posts: