DEV Community

Judy
Judy

Posted on

Calculate the Total Price According to the Cumulative Price Table #eg13

Problem description & analysis

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

Image description
The other is TIER, which stores data as follows:

Image description
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:

Image description
Solution

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

Image description
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.

Top comments (1)

Collapse
 
esproc_spl profile image
Judy

SPL open source address:github.com/SPLWare/esProc