Retrieve records from Rule Table

Question

Rule Table

Have created function which can evaluate different expressions in record depending on input

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, '[>|<|!=|=|<>|<=|>=]',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  <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;

Created corresponding Stored Procedure to call this function for every column and retrieve matching rule

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;

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 ?

Retrieve records from Rule Table

0 Answers