Stage 33 of the customer app from the series Building a customer app in Uniface 10 added a price check module. One of its questions: which product prices changed in the last months, and by how much? The table PRICE_HISTORY has one row per price with a VALID_FROM date. The change is the difference between a row and the row before it for the same product.
Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) on Windows, October 2026. The app uses SQLite through the SLE driver (SQLite 3.50.2).
Without a window function
The first idea in ProcScript is a loop: read all rows ordered by product and date, keep the previous price in a variable, compare. That is about 30 lines with getitem, a product switch and an extra filter for the date. In SQL the "row before" has a name: LAG().
LAG in the inner query
SELECT PRODUCT_ID, VALID_FROM, PRICE_CENTS, LAG(PRICE_CENTS) OVER (PARTITION BY PRODUCT_ID ORDER BY VALID_FROM, HIST_ID) AS PREV FROM PRICE_HISTORY
LAG(PRICE_CENTS) returns the price of the previous row in the same partition, here the same product, in the given order. The first row of a product has no predecessor and gets NULL. With a small example:
PRODUCT_ID VALID_FROM PRICE_CENTS PREV
7 2026-01-01 9000
7 2026-07-23 10000 9000
7 2026-09-01 10000 10000
8 2026-03-01 2500
8 2026-03-01 2400 2500
Two details:
-
ORDER BY VALID_FROM, HIST_ID: two prices on the same day (product 8) would otherwise come in any order. The id makes the order stable. - The window function sits in a subquery because
WHEREcannot use the result of a window function on the same level.
The whole operation
public operation CHANGES
params
numeric pMonths : IN
string pData : OUT
numeric pCount : OUT
string pError : OUT
endparams
pData = ""
pCount = 0
pError = ""
activate "RIGHTS_SVC".REQUIRE("READ", pError)
if ($status < 0)
return -1
endif
if (pMonths < 1 | pMonths > 60)
pError = "The number of months must be between 1 and 60."
return -1
endif
call READ_ROWS("SELECT p.SKU, p.PRODUCT_NAME, h.VALID_FROM, h.PREV, h.PRICE_CENTS, CASE WHEN h.PREV > 0 THEN CAST(ROUND((h.PRICE_CENTS - h.PREV) * 100.0 / h.PREV) AS INTEGER) ELSE 0 END AS PCT FROM (SELECT PRODUCT_ID, VALID_FROM, PRICE_CENTS, LAG(PRICE_CENTS) OVER (PARTITION BY PRODUCT_ID ORDER BY VALID_FROM, HIST_ID) AS PREV FROM PRICE_HISTORY) h JOIN PRODUCT p ON p.PRODUCT_ID = h.PRODUCT_ID WHERE h.PREV IS NOT NULL AND h.PREV <> h.PRICE_CENTS AND h.VALID_FROM >= date('now', 'localtime', '-%%(pMonths) months') ORDER BY 3 DESC, 1", pData, pCount, pError)
if ($status < 0)
return -1
endif
return 0
end
The outer query keeps only real changes (PREV IS NOT NULL AND PREV <> PRICE_CENTS), the last pMonths months, and computes the change in percent as a whole number. pMonths is numeric and checked against 1 to 60 before it goes into the statement. READ_ROWS runs sql/data and returns a list with one item per row, the same helper all services of the stage use.
Window functions need SQLite 3.25 or newer; the SLE driver used here comes with 3.50.2.
The form
entry DO_CHANGES
variables
numeric vCount
string vData, vRow, vError, vText, v1, v2, v3, v4, v5, v6
endvariables
activate "PRICECHK_SVC".CHANGES(MONTHS.PCK_DMY, vData, vCount, vError)
if ($status < 0)
RESULT.PCK_DMY = ""
INFO.PCK_DMY = vError
return -1
endif
vText = ""
forlist vRow in vData
getitem v1, vRow, 1
getitem v2, vRow, 2
getitem v3, vRow, 3
getitem v4, vRow, 4
getitem v5, vRow, 5
getitem v6, vRow, 6
call FMT(v4, 100, v4)
call FMT(v5, 100, v5)
call ADD_LINE(vText, "%%(v1) %%(v2): valid from %%(v3): %%(v5) instead of %%(v4) (%%(v6) percent)")
endfor
if (vText = "")
vText = "No price changes."
endif
RESULT.PCK_DMY = vText
INFO.PCK_DMY = "%%(vCount) change(s)."
return 0
end
FMT turns cents into a number with two decimals, ADD_LINE appends a line to the text field. With the click test data the line reads:
TS33-A Part A: valid from 2026-07-23: 100,00 instead of 90,00 (11 percent)
The first version said from 2026-07-23 100,00 instead of 90,00. In the click test "from" read like a quantity, so the text now says "valid from".
Notes
- The tests of the stage compute their expected values from a copy of the database with the same SQL, then call the operation inside Uniface and compare.
- A price that goes back to an older value is a change as well; a row with the same price as before (product 7 on 2026-09-01) is not.
Top comments (0)