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.ORDERSLOWER+1,HIGHERLOWER+1):ORDERS,PRICE:CHARGE)) 
Explanation:
A1 Connect to oracle database.
A2 Return the result table sequence.
A3 Return the result table sequence and autoclose 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.
https://stackoverflow.com/questions/64131089/divideanumberintomultiplebands