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 7FD579FB2E2 for ; Mon, 10 Jul 2006 13:36:21 -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 00219-03 for ; Mon, 10 Jul 2006 13:36:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 9FC3C9FB259 for ; Mon, 10 Jul 2006 13:36:12 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id y25so704366nfb for ; Mon, 10 Jul 2006 09:36:11 -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=iuNT1uvkttLqkJQXAfTchcZ2OojDLjxsQcxzyq8+S+useoyrSgSeMor2oqkZG5oUFUqu8nqM8+3VFrfew4qs213QTusWD7hvmqwf0glRcBCzb2QolhDdyErxGgzG9d8DknrEdJgE1w+HilPHIg+5z9XQ9SstIRtv0nq6qOiJ204= Received: by 10.78.156.6 with SMTP id d6mr1777158hue; Mon, 10 Jul 2006 09:36:10 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Mon, 10 Jul 2006 09:36:10 -0700 (PDT) Message-ID: Date: Mon, 10 Jul 2006 11:36:10 -0500 From: "Aaron Bono" To: "Phillip Smith" Subject: Re: Select Maths Cc: pgsql-sql@postgresql.org In-Reply-To: <000001c6a3f1$84cf1ed0$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_33805_14713337.1152549370808" References: <000001c6a3f1$84cf1ed0$9b0014ac@ITPhil> X-Google-Sender-Auth: 1f1e60ff8921c3f0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/148 X-Sequence-Number: 25722 ------=_Part_33805_14713337.1152549370808 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/10/06, Phillip Smith wrote: > > Beautiful =96 Works a treat. Thanks Aaron. > > > > A follow-on problem now=85 I have the below column in the select, but I n= eed > to validate the value across all 3 rules =96 I need to assign it to a > variable!! > > > > Example =96 my pqty function calculates a value less than the suppliers > minimum order qty (and therefore fails the first CASE below), I need to s= et > the column to a new value (stock.purchase_unit) =96 That's all OK. But I > need to check this new value against the remaining 2 CASE's=85 > > > > > > CASE WHEN pqty(stock.code) < stock.purchase_unit THEN stock.purchase_uni= t > > --^^^-- 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 o= f > 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", > > > 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 ------=_Part_33805_14713337.1152549370808 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:

Beautiful =96 Works a treat. Thank= s Aaron.

 

A follow-on problem now=85 I have = the below column in the select, but I need to validate the value across all 3 r= ules =96  I need to assign it to a variable!!

 

Example =96 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) =96 That's all OK. But I need to check this new value against the remaining 2 CASE's=85

 

<SNIP>

CASE  WHEN pqty(stock.code) < stock.purchase_unit THEN stock.purchase_unit

        =     --^^^-- Check that our suggested purchase qty is greater than then suppliers minimu= m 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 ura= te(stock.code) * creditors.review_cycle

        =     --^^^-- Check that our suggested purchase qty is greater than our Usage Rate x Cred= itor 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 try= ing to do here.

-Aaron 

------=_Part_33805_14713337.1152549370808--