"Problem description & analysis We have two database table. One is ORDERINFO, which has data .."

blackduckie RaqForum 28 No.
361 View • 3 Years ago

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))