RE: querry..

<SPAN

class=376071912-31052001>hi
<SPAN

class=376071912-31052001> 
well,
one way to do it is to create a stored function
<SPAN

class=376071912-31052001> 

create
or replace package pkg_select
<SPAN

class=376071912-31052001>is
<SPAN

class=376071912-31052001>function cmb(p1 varchar2, p2 varchar2)
return
varchar2;
<SPAN

class=376071912-31052001>end;
<SPAN

class=376071912-31052001> 
create
or replace package body pkg_select
<SPAN

class=376071912-31052001>is
<SPAN

class=376071912-31052001>function cmb(p1 varchar2, p2 varchar2)
return
varchar2
<SPAN

class=376071912-31052001>is
<SPAN

class=376071912-31052001>begin
 
if p1 = 'A' and instr(p2, 'A|B|C|D|E') >0 then
<SPAN

class=376071912-31052001>    return 'TRUE';

 
elsif p1 = 'B' and instr(p2, 'A|B|C|D|E|F|G|H|etc...') >0 then

<SPAN

class=376071912-31052001>    return 'TRUE';  
else
<SPAN

class=376071912-31052001>    return 'FALSE';  
end if;
<SPAN

class=376071912-31052001>exception
 
when others then
<SPAN

class=376071912-31052001>    return SQLERRM; end
cmb;
end
pkg_select;
<SPAN

class=376071912-31052001> 
then
do
<SPAN

class=376071912-31052001> 
<SPAN

class=376071912-31052001><FONT color=#0000ff face=Arial size=2>select col1, col2 from table1 where pkg_select.cmb(col1, col2) = 'TRUE';
<SPAN

class=376071912-31052001> 
This
keeps your select nice and simple, also you can create a function-based index to give fast performance.
<SPAN

class=376071912-31052001> 
<SPAN

class=376071912-31052001>Rgds
<SPAN

class=376071912-31052001>Greg
<SPAN

class=376071912-31052001> 

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Saurabh Sharma   [mailto:saurabhs_at_fcsltd.com]Sent: Thursday, 31 May 2001   09:50To: Multiple recipients of list ORACLE-LSubject:   querry..
  hi list,
   
  how can i select two columns from
  a table based on condition that they are selected in specified   combinations.
   
  let me..
  table 1 has 2 columns col1, col2.
  both cols have values , say, alphabets. a,b,c,d,e,f,...   i'want to select
like
   
  FOR VALUE OF COL1 IN A, 
  col2 must fetch only between A-E
  for value of col1 in B, col2 must
  be between A-X
  --
   and so on..
   
  i want to define this
  combination, so i should get only these pair of values.    
  any suggestions.
  thanks.
   
  <FONT color=#0000ff face=Courier
size=2>saurabh Received on Thu May 31 2001 - 08:41:57 CDT