Creating Decision Table with Return Values

[rindia]

Definition: Decision table creates the business rules in a tabular format for automating the decisions.

We can create decision table based on the sources: Multiple tables or Table type or Information view (Attribute or Analytic or Calculation view).

In this part, I use single (one) table only.

Consider the below table EMPLOYEE having columns (EID, ENAME, EDEPT, SAL, COMM) with 35 records.

                                                                      

Now I will calculate total salary based on some conditions as shown below.

If salary is <= 9000 then total salary = salary + (commission * 1.5) else total salary = salary + commission.

So based on the condition (salary), an action (total salary) will be calculated accordingly and the attributes we have in decision table are

EID, ENAME, SAL, COMM, TOTAL_SAL.

To create a decision table, we need to have at least one condition and action.

Steps to create Decision Table:

1. Right click the package where you want to create the decision table, select New and choose Decision table.

                                  

2. In next screen give the name and description for decision table and click Next  as shown below. 

3. In next screen, choose the table you want on which you create business rules.

(Note: Remember that we can choose table type or Information view, but I choose only single table for simplicity.

Also note that combination of above source is NOT possible i.e. using tables and table type or using tables and information views, etc.)

                               

4. In next screen, you can see the data foundation along with the Output as shown above on right side.

Select the columns you want (EID, ENAME, SAL, COMM) right click and select “Add as Attribute”. As we do not have the column total salary in the table, we create a parameter called TOTAL_SAL  which is calculated depending upon the business rules on salary (SAL) based as listed below:

If salary is <= 9000 then total salary = salary + (comm * 1.5) else total salary = salary + comm.

5. In the output , right click Parameter and choose New, give name, description, Data type as required.

                    

6. In the output, under the Attributes, right click the “SAL” and choose “Add as conditions” and

under the parameter, right click the “TOTAL_SAL” and choose “Add as Actions” as shown below.

                                 

7. Now the output of Data foundation should look like as below and click on below tab Decision Table.

                      

8. Upon clicking Decision table, you will two columns (SAL, TOTAL_SAL). SAL is the condition and TOTAL_SAL is the action.

Remember that there should be at least one condition and one action is required to see this else there will be message

saying that “Decision Table should have at least one condition and one action”.

                        

9. Double click the cell of condition SAL and enter the expression <= 9000,

right click the cell of action TOTAL_SAL and choose “Set Dynamic Value” and follow the below steps.

                       

10. Now save and validate, activate the decision table.

Upon successful activation, it will create procedure in schema _SYS_BIC as  “srk/DT_EMPLOYE” where DT_EMPLOYEE is decision table name in package srk and one table type "srk/DT_EMPLOYEE/TT" as we used parameter “TOTAL_SAL” as action.

                           

11. Now execute the procedure in SQLEditor as

CALL "_SYS_BIC"."srk/DT_EMPLOYEE"(NULL);

To see the result, go to schema _SYS_BIC, Expand Column Views, "srk/DT_EMPLOYEE/RV".

This will hold the results which contain the columns as defined in Decision table.

The column TOTAL_SAL is calculated accordingly based on the business condition SAL.

(If salary is <= 9000 then total salary = salary + (commission * 1.5) else total salary = salary + commission.)

                                       

That's it. We are done with the creation of Decision table with return values.

Hope you will like this. Thank You for your time.