Feasibility of dynamic conditional rule [message #336223] |
Fri, 25 July 2008 05:16  |
scorpio_biker
Messages: 154 Registered: November 2005 Location: Kent, England
|
Senior Member |
|
|
Hi,
I'm trying to work out if I can dynamically test a variables value by constructing an if statement from a table.
For example, if a database field is filled in with an age, I want to obtain a rule from a table and check that criteria against the database field.
For example, a customer age is stored as 60 and their favorite colour is stored as blue.
I have a rule that says we block customers over 55 with a favorite colour of blue. However I need to be able to alter this rule without having to change code (for example to change the colour to red).
I need to take the rule from the table and check it against the applicable database field(s) and determine the outcome.
The rules will be grouped together (over 55 and blue/under 40 and own a parrot - these are just examples by the way, not real data ).
I've given it a go with dynamic sql but in constructing the statement I have to code in the database field name - drv_age in my example (or I do the way I have tried it).
Can anyone give me options/alternatives on how to take a rule from a table and use purely that rule to check database values for fields named in the rule.
I've attached my small test code that I've been trying. In this example drv_age is my problem as I don't want to specify the field name in the code (I want to get it from my table).
Thanks in advance if anyone can help me.
drop table quote_block_rules
/
create table quote_block_rules
(block_set number(5),
tia_field varchar2(50),
internet_field varchar2(50),
datatype varchar2(15),
blocking_text varchar2(500))
/
insert into quote_block_rules
values (1,'drv_age','drv_age','number','> 60')
/
insert into quote_block_rules
values (2,'drv_age','drv_age','number','> 70')
/
declare
cursor main_cur is
select distinct block_set from quote_block_rules;
cursor block_set(p_block_set in number) is
select internet_field, blocking_text, datatype
from quote_block_rules
where block_set = p_block_set;
drv_age number(2);
v_test varchar2(1000);
local_var VARCHAR2(10);
begin
-- drv_age simulates my database field that I need to check
drv_age := 65;
for main_rec in main_cur
loop
local_var := 'false';
for block_rec in block_set(main_rec.block_set)
loop
-- I don't want to have to specify drv_age in this statement?
v_test := 'declare ' || block_rec.internet_field || ' ' || block_rec.datatype || ' := ' || drv_age || ';' ||
' begin if ' || block_rec.internet_field || ' ' || block_rec.blocking_text ||
' then :local_var := ''true''; end if; end;';
end loop;
dbms_output.put_line(v_test);
execute immediate (v_test) using out local_var;
dbms_output.put_line(local_var);
end loop;
end;
|
|
|
Re: Feasibility of dynamic conditional rule [message #336227 is a reply to message #336223] |
Fri, 25 July 2008 05:55   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I do something similar to that, where I put a formula into a table and then execute that formula with dynamic SQL.
The way I handled variables was to write them in [] in the formula, and then replace them before the execution.
You could do the same here, like :
declare
cursor main_cur is
select distinct block_set from quote_block_rules;
cursor block_set(p_block_set in number) is
select internet_field, blocking_text, datatype
from quote_block_rules
where block_set = p_block_set;
drv_age number(2);
animal VARCHAR2(100);
color VARCHAR2(100);
v_test varchar2(1000);
local_var VARCHAR2(10);
begin
-- drv_age simulates my database field that I need to check
drv_age := 65;
for main_rec in main_cur
loop
local_var := 'false';
for block_rec in block_set(main_rec.block_set)
loop
-- I don't want to have to specify drv_age in this statement?
v_test := 'declare ' || block_rec.internet_field || ' ' || block_rec.datatype || ' := [' || block_rec.internet_field || '];' ||
' begin if ' || block_rec.internet_field || ' ' || block_rec.blocking_text ||
' then :local_var := ''true''; end if; end;';
/* Replace all 'rule Variables' here */
v_test := REPLACE(v_test,'[drv_age]' ,drv_age);
v_test := REPLACE(v_test,'[color]' ,color);
v_test := REPLACE(v_test,'[animal]' ,animal);
end loop;
dbms_output.put_line(v_test);
execute immediate (v_test) using out local_var;
dbms_output.put_line(local_var);
end loop;
end;
|
|
|
|