Beautiful – Works a treat. Thanks Aaron.
A follow-on problem now… I have the below column in the select, but I need to validate the value across all 3 rules – I need to assign it to a variable!!
Example – my pqty function calculates a value less than the suppliers minimum order qty (and therefore fails the first CASE below), I need to set the column to a new value (stock.purchase_unit) – That's all OK. But I need to check this new value against the remaining 2 CASE's…
<SNIP>
CASE WHEN pqty(stock.code) < stock.purchase_unit THEN stock.purchase_unit
--^^^-- Check that our suggested purchase qty is greater than then suppliers minimum order qty
WHEN MOD(pqty(stock.code), stock.box_qty) > 0 THEN stock.box_qty * ROUND(CAST(pqty(stock.code) AS DOUBLE PRECISION) / stock.box_qty)
--^^^-- Check that our suggested purchase qty is a multiple of the box qty
WHEN pqty(stock.code) < (urate(stock.code) * creditors.review_cycle) THEN urate(stock.code) * creditors.review_cycle
--^^^-- Check that our suggested purchase qty is greater than our Usage Rate x Creditor Review Cycle
END AS "pqty",
<SNIP>