# 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:

 CUST ORDERS CUST1 400 CUST2 1200 CUST3 40 CUST4 2000 CUST5 700

The other is TIER, which stores data as follows:

 TIER LOWER HIGHER PRICE TIER1 1 250 50 TIER2 251 500 45 TIER3 501 1000 40 TIER4 1001 10000 30

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:

 CUST ORDERS CHARGE CUST1 250 50 CUST1 150 45 CUST2 250 50 CUST2 250 45 CUST2 500 40 CUST2 200 30 CUST3 40 50 CUST4 250 50 CUST4 250 45 CUST4 500 40 CUST4 1000 30 CUST5 250 50 CUST5 250 45 CUST5 200 40

Solution

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

 A 1 =connect("oracle") 2 =A1.query@("SELECT * FROM ORDERINFO") 3 =A1.query@x("SELECT * FROM TIER") 4 =A2.conj((A3.select(LOWER<=A2.ORDERS)).new(A2.CUST,if(HIGHER>=A2.ORDERS,A2.ORDERS-LOWER+1,HIGHER-LOWER+1):ORDERS,PRICE:CHARGE))

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.