Re: Select Maths

On 7/10/06, Phillip Smith <phillips(at)weatherbeeta(dot)com(dot)au> wrote:
>
> Example:
>
> Funcation pqty(stock.code) calculates a value of 0 for a particular
> product. This fails the last CASE that makes sure the pqty() value is
> greater than our Usage Rate * Review Cycle – in this case is 3. But that
> is less than our Minimum Order Qty (First CASE) and not a multiple of our
> Box Qty (Second CASE)
>
> Another example could be that pqty() calculates less than the Minimum
> Order Qty (fails first CASE) so we raise it to the Minimum Order Qty, but
> that new value could fail either or both of the second CASE's.
>
> Minimum Order Qty = stock.purchase_unit
> Box Qty = stock.box_qty
>
> I guess a better way to word it is that because pqty() returns a
> calculated value each time and I can't take that value and assign it to a
> variable, then use that variable. If I was writing VB or similar I'd want
> something like:
>
> *intPurchaseQty* = pqty(stock.code)
>
> CASE WHEN *intPurchaseQty* < stock.purchase_unit THEN *intPurchaseQty* =
> stock.purchase_unit
>
> WHEN MOD(*intPurchaseQty*, stock.box_qty) > 0 THEN *intPurchaseQty* =
> stock.box_qty * ROUND(CAST(*intPurchaseQty* AS DOUBLE PRECISION) /
> stock.box_qty)
>
> WHEN *intPurchaseQty* < (urate(stock.code) * creditors.review_cycle) THEN
> *intPurchaseQty* = urate(stock.code) * creditors.review_cycle
>
> END
>
> COLUMN = intPurchaseQty AS "pqty",
>
> I hope that makes it a lighter shade of mud!!
>

Why wouldn't you be able to do this in a function? Pass in stock.code,
stock.purchase_unit, stock.box_qty and creditors.review_cycle. You can then
use variables in the function, right?

-Aaron