title: Retrieve records from Rule Table
body: <p>Rule Table</p>
  <p><a href="https://i.stack.imgur.com/xCL5K.png" rel="nofollow noreferrer">show rule table</a></p>
  <p>Have created function which can evaluate different expressions in record depending on input</p>
  <pre><code>create or replace FUNCTION EVALUATERULE(colvalue varchar2 ,inputvalue varchar2)
  return number
  as
  temp_result varchar2(1000);
  result  number; 
  begin
  if colvalue = '*' or colvalue = 'NULL'
  then
  result := 1;
  elsif regexp_instr(colvalue, '[&gt;|&lt;|!=|=|&lt;&gt;|&lt;=|&gt;=]',1,1,0,'i')=1 and regexp_like(inputvalue,'[^a-zA-Z]') then
      temp_result :='select decode(count(1),0,0,1)  from (select r1  from (select (r-1)/100 r1 from (select rownum r from all_objects where rownum  &lt;100002)) where r1'|| colvalue ||') where r1 = '|| inputvalue;
    execute immediate temp_result into result;
  elsif regexp_instr(colvalue, 'in',1,1,0,'i')=1 then  
    temp_result :='select decode(count(1),0,0,1)  from  dual where regexp_like('''||colvalue||''','''|| inputvalue||''')';
    execute immediate temp_result into result;
  elsif regexp_instr(colvalue, 'not',1,1,0,'i')=1 then  
        temp_result :='select decode(count(1),0,1,0)  from  dual where regexp_like('''||colvalue||''','''|| inputvalue||''')';
     execute immediate temp_result into result;
  elsif regexp_like(colvalue, '^\*[a-zA-Z]') or regexp_like(colvalue, '[a-zA-Z]\*$') then        
    temp_result :='select decode(count(1),0,0,1)  from  dual where  '''||inputvalue||'''  like regexp_replace('''||colvalue||''',''\*'',''%'',1,0,''i'')';
     execute immediate temp_result into result; 
  else
     temp_result :='select decode(count(1),0,0,1)  from  dual where '''||colvalue||'''='''||inputvalue||'''';
    execute immediate temp_result into result; 
  end if; 
  return result;
  END EVALUATERULE;
  </code></pre>
  <p>Created corresponding Stored Procedure to call this function for every column and retrieve matching rule</p>
  <pre><code>create or replace PROCEDURE GET_CUSTOMER_RULES(
      SCO_CUSTOMER_CD_I IN VARCHAR2,
      MAX_VOL_I IN VARCHAR2,
      MIN_VOL_I IN VARCHAR2,
      MIN_WT_I IN VARCHAR2,
      MAX_WT_I IN VARCHAR2,
      MIN_CARTONS_I IN VARCHAR2,
      MAX_CARTONS_I IN VARCHAR2,
      MIN_PALLETS_I IN VARCHAR2,
      MAX_PALLETS_I IN VARCHAR2,
      PRIORITY_I IN VARCHAR2,
      ORDER_TYPE_I IN VARCHAR2,
      ROUTE_I IN VARCHAR2,
      SHIPTO_COUNTRY_I IN VARCHAR2,
      RULE_SEQ_O OUT VARCHAR2,
      CARRIER_O OUT VARCHAR2,
      SHIP_VIA_O OUT VARCHAR2,
      LOGISTIC_UNIT_O OUT VARCHAR2,
      CARTON_LABEL_TYPE_O OUT VARCHAR2 
  ) AS 
  BEGIN
  select  RULE_SEQ,CARRIER,SHIP_VIA,LOGISTIC_UNIT,CARTON_LABEL_TYPE INTO RULE_SEQ_O,CARRIER_O,SHIP_VIA_O,LOGISTIC_UNIT_O,CARTON_LABEL_TYPE_O
          from (select  t.*,
          EVALUATERULE(SCO_CUSTOMER_CD, SCO_CUSTOMER_CD_I) cd,
          EVALUATERULE(MAX_VOL, MAX_VOL_I) maxVol,
          EVALUATERULE(MIN_VOL, MIN_VOL_I) minVol,
          EVALUATERULE(MIN_WT, MIN_WT_I) minWT,
          EVALUATERULE(MAX_WT, MAX_WT_I) maxWT,
          EVALUATERULE(MIN_CARTONS, MIN_CARTONS_I) minCARTONS,
          EVALUATERULE(MAX_CARTONS, MAX_CARTONS_I) maxCARTONS,
          EVALUATERULE(MIN_PALLETS, MIN_PALLETS_I) minPALLETS,
          EVALUATERULE(MAX_PALLETS, MAX_PALLETS_I) maxPALLETS,
          EVALUATERULE(PRIORITY, PRIORITY_I) prty,
          EVALUATERULE(ORDER_TYPE, ORDER_TYPE_I) ordTyp,
          EVALUATERULE(ROUTE, ROUTE_I) rte,  
          EVALUATERULE(SHIPTO_COUNTRY, SHIPTO_COUNTRY_I) shpToCty
          from SCO_CS_CUSTOM_CARRIER_RULESBKP t) where cd=1 and maxVol=1 and minVol=1 and minWT =1 and maxWT = 1 and minCARTONS = 1 and maxCARTONS = 1 and minPALLETS = 1 and maxPALLETS = 1 and prty=1 and ordTyp =1 
  and rte = 1 and shpToCty=1 ORDER BY RULE_SEQ ASC FETCH FIRST 1 ROW ONLY;
  END GET_CUSTOMER_RULES;
  </code></pre>
  <p>This is working as expected but the question is whether there is better way to do this then to evaluate through function for every column ?</p>