I attached my sample data and in summary expected calculation dynamically.

For example: Table 1, Table 2 is the source data and Lookup table (Business_rule) had calculation rule under calculation_rule. so using Table 1, Table 2 data user expected apply calculation_rule and want to get result as per in Output table. Source table are in Oracle Database.

I tried with all algorithms like xmltable etc. but not working.

CodePudding user response：

Here table 1 = employees; table 2 = departments; output is dbms_output.

DECLARE
v_sql VARCHAR2(1000);
lc_cur SYS_REFCURSOR;
ln_employee_id VARCHAR2(10);
ln_department_id NUMBER;
ln_calc_amt NUMBER;
BEGIN
FOR rec IN (SELECT e.department_id, br.metric_code 
            FROM employees e INNER JOIN business_rule br on e.department_id = br.department_id 
          )   
LOOP
SELECT 'SELECT e.employee_id, d.department_id,'||calculation_rule||' 
           from employees e inner join departments d on e.department_id = d.department_id 
           where e.department_id = '||rec.department_id
    INTO v_sql
 FROM business_rule WHERE department_id = rec.department_id;   
 
 OPEN lc_cur FOR v_sql;
 LOOP
   FETCH lc_cur INTO ln_employee_id, ln_department_id, ln_calc_amt;
   EXIT WHEN lc_cur%NOTFOUND;
   dbms_output.put_line(ln_employee_id||' - '||ln_department_id||' - '||rec.metric_code||' - '||ln_calc_amt);
 END LOOP;
 CLOSE lc_cur;
END LOOP;
END;