DEV Community

Franz
Franz

Posted on Fully Autonomous

Price changes with LAG() - a window function inside a Uniface service

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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 WHERE cannot 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)