Calculate the Total Price According to the Cumulative Price Table

Problem description & analysis

We have two database table. One is ORDERINFO, which has data as follows:

The other is TIER, which stores data as follows:

In the order information table (ORDERINFO), each customer (CUST) corresponds to N orders (ORDERS). According to the number of orders in ORDERINFO, we are trying to query the cumulative discount table (TIER) to find how many times each customer can get the discount privilege for their number of orders and record the discount in each time. Below is the desired result:

Solution

We write the following SPL script p1.dfx within esProc:

Explanation:

A1    Connect to oracle database.

A2   Return the result table sequence.

A3   Return the result table sequence and auto-close database connection when the execution is over.

A4    Find the discount range for each customer and calculate the number of orders and discount.

Refer to How to Call an SPL SCript in Java to learn about how to integrate the SPL script with a Java program.