X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2239F9FB2E3 for ; Mon, 10 Jul 2006 21:31:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 20480-09 for ; Mon, 10 Jul 2006 21:30:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from weatherbeeta.com.au (mail.weatherbeeta.com.au [202.154.127.242]) by postgresql.org (Postfix) with SMTP id 30C9C9FB2D5 for ; Mon, 10 Jul 2006 21:30:47 -0300 (ADT) Received: (qmail 26031 invoked from network); 11 Jul 2006 00:30:43 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;16a447f6c3a3db86f84486fb6859ffdc; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 11 Jul 2006 00:30:43 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d5604f8 for pgsql-sql@postgresql.org; Tue, 11 Jul 2006 10:28:57 +1000 From: "Phillip Smith" To: Subject: Re: Select Maths Date: Tue, 11 Jul 2006 10:30:21 +1000 Message-ID: <006f01c6a481$31b9e280$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0070_01C6A4D5.0365F280" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1152577843.430-26016-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.27 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/165 X-Sequence-Number: 25739 This is a multi-part message in MIME format. ------=_NextPart_000_0070_01C6A4D5.0365F280 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D stock.purchase_unit Box Qty =3D 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 =3D pqty(stock.code) CASE WHEN intPurchaseQty < stock.purchase_unit THEN intPurchaseQty =3D stock.purchase_unit WHEN MOD(intPurchaseQty, stock.box_qty) > 0 THEN intPurchaseQty =3D stock.box_qty * ROUND(CAST(intPurchaseQty AS DOUBLE PRECISION) / stock.box_qty) WHEN intPurchaseQty < (urate(stock.code) * creditors.review_cycle) THEN intPurchaseQty =3D urate(stock.code) * creditors.review_cycle END COLUMN =3D intPurchaseQty AS "pqty", I hope that makes it a lighter shade of mud!! =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Aaron Bono Sent: Tuesday, 11 July 2006 02:36 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Select Maths 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.=20 -Aaron=20 =20 *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ------=_NextPart_000_0070_01C6A4D5.0365F280 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D stock.purchase_unit
Box Qty =3D 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 =3D pqty(stock.code)

CASE  WHEN intPurchaseQty < = stock.purchase_unit THEN intPurchaseQty =3D stock.purchase_unit

WHEN MOD(intPurchaseQty, stock.box_qty) > 0 THEN = intPurchaseQty =3D stock.box_qty * ROUND(CAST(intPurchaseQty AS DOUBLE = PRECISION) / stock.box_qty)

WHEN intPurchaseQty < (urate(stock.code) * creditors.review_cycle) THEN intPurchaseQty =3D urate(stock.code) = * creditors.review_cycle

END

COLUMN =3D intPurchaseQty AS "pqty",

I hope that makes it a lighter shade of mud!!

 

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] = On Behalf Of Aaron Bono
Sent: Tuesday, 11 July 2006 02:36
To: Phillip Smith
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] Select = Maths


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 

 


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
------=_NextPart_000_0070_01C6A4D5.0365F280--