Re: Select Maths

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>