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 11:36:10 -0500
Message-ID: <bf05e51c0607100936q393bb524qe4f7c202ec16a25a@mail.gmail.com> (raw)
In-Reply-To: <000001c6a3f1$84cf1ed0$9b0014ac@ITPhil>
References: <bf05e51c0607070136he5519b9x96e97a73a6483a2a@mail.gmail.com>
<000001c6a3f1$84cf1ed0$9b0014ac@ITPhil>
On 7/10/06, Phillip Smith <phillips@weatherbeeta.com.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
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: <bf05e51c0607100936q393bb524qe4f7c202ec16a25a@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