Re: Select Maths

On 7/10/06, Phillip Smith <phillips(at)weatherbeeta(dot)com(dot)au> wrote:
>
> 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>
>

Can you provide example values and show where it is and is not working? I
am not quite sure what you are trying to do here.

-Aaron