From: Aaron Bono <postgresql@aranya.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Select Maths
Date: Mon, 10 Jul 2006 22:41:34 -0500
Message-ID: <bf05e51c0607102041nc10f58fmeb1869b3d38c677e@mail.gmail.com> (raw)
In-Reply-To: <006f01c6a481$31b9e280$9b0014ac@ITPhil>
References: <006f01c6a481$31b9e280$9b0014ac@ITPhil>
On 7/10/06, Phillip Smith <phillips@weatherbeeta.com.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, phillips@weatherbeeta.com.au
Subject: Re: Select Maths
In-Reply-To: <bf05e51c0607102041nc10f58fmeb1869b3d38c677e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox