Re: Sql Query

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Mon, 20 Mar 2006 10:14:21 -0800 (PST)
Message-ID: <20060320181421.59778.qmail@web51315.mail.yahoo.com>

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_at_gmail.com> 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.
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 20 2006 - 12:14:21 CST