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 974919FB2E3 for ; Tue, 11 Jul 2006 00:41:40 -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 56994-07 for ; Tue, 11 Jul 2006 00:41:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id F240C9FB2E2 for ; Tue, 11 Jul 2006 00:41:35 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l23so82935nfc for ; Mon, 10 Jul 2006 20:41:34 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=JFTBpfiKGp/aZ8yqgsJiGyBXUbQ2OCihBTWEn6OGp73zQN7HjEofIbbuyEUR0Pu7xM8+o6vqlANDKQz1Hex0Zz9wbDggRhahaauICD2BLvJx46WupTpBrX9HUpMAsNYpKnVOsLPOzYGFSEhRpbRoNwHLNIr9zqiJmaGds1elT+I= Received: by 10.78.177.3 with SMTP id z3mr1975576hue; Mon, 10 Jul 2006 20:41:34 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Mon, 10 Jul 2006 20:41:34 -0700 (PDT) Message-ID: Date: Mon, 10 Jul 2006 22:41:34 -0500 From: "Aaron Bono" To: "Phillip Smith" Subject: Re: Select Maths Cc: pgsql-sql@postgresql.org In-Reply-To: <006f01c6a481$31b9e280$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_44142_15148792.1152589294666" References: <006f01c6a481$31b9e280$9b0014ac@ITPhil> X-Google-Sender-Auth: 7d25b66f92eae6d3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/169 X-Sequence-Number: 25743 ------=_Part_44142_15148792.1152589294666 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/10/06, Phillip Smith 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 =96 in this case is 3. But tha= t > 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!! > 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 the= n use variables in the function, right? -Aaron ------=_Part_44142_15148792.1152589294666 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/10/06, Phillip Smith <phillips@weatherbeeta.com.au> w= rote:

Examp= le:

Funca= tion pqty(stock.code) calcul= ates a value of 0 for a particular product. This fails the last CASE that makes sure the pqty()<= /span> value is greater than our Usage Rate * Review Cycle =96 in thi= s case is 3. But that is less than our Minimum Order Qty (First CASE) and not a multi= ple of our Box Qty (Second CASE)

Anoth= er 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 gue= ss 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 intPurchaseQt= y =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 hop= e that makes it a lighter shade of mud!!
=

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

-Aaron ------=_Part_44142_15148792.1152589294666--