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.

Q & A Collection

https://stackoverflow.com/questions/64131089/divide-a-number-into-multiple-bands