From phillips@weatherbeeta.com.au Fri Jul 7 06:54:58 2006 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 669A99FA6B0 for ; Fri, 7 Jul 2006 03:54:58 -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 35921-10 for ; Fri, 7 Jul 2006 03:54: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 F32799FA64B for ; Fri, 7 Jul 2006 03:54:48 -0300 (ADT) Received: (qmail 25087 invoked from network); 7 Jul 2006 06:54:47 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;470fd204cae27357b2c106e496cab23f; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 7 Jul 2006 06:54:47 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d55aae3 for pgsql-sql@postgresql.org; Fri, 7 Jul 2006 16:52:28 +1000 From: "Phillip Smith" To: Subject: Select Maths Date: Fri, 7 Jul 2006 16:53:52 +1000 Message-ID: <012b01c6a192$1b827730$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_012C_01C6A1E5.ED2E8730" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 In-Reply-To: Importance: Normal 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-1152255287.345-25082-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.10 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/79 X-Sequence-Number: 25653 This is a multi-part message in MIME format. ------=_NextPart_000_012C_01C6A1E5.ED2E8730 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: 7bit Hi again, Same SELECT query as before, different area of it. I have a function that calculates the recommended purchase order quantity for a stock item based off various other values and functions: pqty(stock.code) AS "pqty" This needs to be rounded up / down to the nearest multiple of the purchase unit quantity for that product - It's Friday afternoon and my head has refused to help me work out the maths all afternoon! Example: Pqty = 60 Purchase Unit = 25 Pqty needs to be rounded down to 50. I guess I'm also asking if I should do this in the Pqty function or in the SELECT query to optimize the result? Thanks all - Enjoy your weekends I hope! Cheers, -p *******************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_012C_01C6A1E5.ED2E8730 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi again,

Same SELECT query as before, = different area of it… I have a function that calculates the recommended purchase = order quantity for a stock item based off various other values and = functions:

pqty(stock.code) AS "pqty"

This needs to be rounded up / down = to the nearest multiple of the purchase unit quantity for that product = – It’s Friday afternoon and my head has refused to help me work out = the maths all afternoon!

Example:

Pqty =3D 60

Purchase Unit =3D 25

Pqty needs to be rounded down to = 50.

I guess I’m also asking if I = should do this in the Pqty function or in the SELECT query to optimize the = result?

Thanks all – Enjoy your = weekends I hope!

Cheers,

-p

*******************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_012C_01C6A1E5.ED2E8730-- From rdeleonp@gmail.com Fri Jul 7 08:21:02 2006 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 B200B9FA65C for ; Fri, 7 Jul 2006 05:21:02 -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 28975-05 for ; Fri, 7 Jul 2006 05:20:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 436979FA634 for ; Fri, 7 Jul 2006 05:20:58 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so2997961ugf for ; Fri, 07 Jul 2006 01:20:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Xp9/c7iY8cFf+70l3pqI0W7L8HLpERZWUGAirjf8DeFQmavu0gC5TUtcTPU6qFlgUn0hvFcMD2g8NBA821Tv34Ny5eUQgwji50QLOahdQCeIfzoSnFBpqD8VTNYyGhMXMSWREeTlih7CZFZpJJM3UKCTX89WDWQ6Jvg/wJL3w9g= Received: by 10.67.29.12 with SMTP id g12mr1696693ugj; Fri, 07 Jul 2006 01:20:57 -0700 (PDT) Received: by 10.67.24.20 with HTTP; Fri, 7 Jul 2006 01:20:57 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2006 03:20:57 -0500 From: "Rodrigo De Leon" Reply-To: rdeleonp@gmail.com, rdeleonp@yahoo.com To: "Phillip Smith" Subject: Re: Select Maths Cc: pgsql-sql@postgresql.org In-Reply-To: <012b01c6a192$1b827730$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <012b01c6a192$1b827730$9b0014ac@ITPhil> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/82 X-Sequence-Number: 25656 On 7/7/06, Phillip Smith wrote: > Hi again, G'day (it's 03:21 on a friday here). > Same SELECT query as before, different area of it=85 I have a function th= at > calculates the recommended purchase order quantity for a stock item based > off various other values and functions: > > pqty(stock.code) AS "pqty" > > This needs to be rounded up / down to the nearest multiple of the purchas= e > unit quantity for that product =96 It's Friday afternoon and my head has > refused to help me work out the maths all afternoon! > > Example: > > Pqty =3D 60 > > Purchase Unit =3D 25 > > Pqty needs to be rounded down to 50. create or replace function roundupdown(pqty int, punit int) returns int as $$ select ((case when $1<$2 then $2 else $1 end)/$2::float)::int*$2; $$ language 'sql'; -- test select roundupdown(s.x,25) as pqty , (s.x/25::float) as near from generate_series(1,100) s(x); > I guess I'm also asking if I should do this in the Pqty function or in th= e > SELECT query to optimize the result? Whatever suits your usage pattern, I guess. > Thanks all =96 Enjoy your weekends I hope! > > Cheers, > > -p Same to you. Regards, Rodrigo From postgresql@aranya.com Fri Jul 7 08:37:03 2006 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 791209FA68D for ; Fri, 7 Jul 2006 05:37:03 -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 38565-08 for ; Fri, 7 Jul 2006 05:36:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 39D209FA65C for ; Fri, 7 Jul 2006 05:36:54 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n15so124989nfc for ; Fri, 07 Jul 2006 01:36:53 -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=X01MiKZbRzwQi76m4iUhQm9NQ51Da0Qzv5ay7wNtD2q8owTwytBi/EHNWAHeWicp0xEtnh1V6I6StwErQkStLDF7FbKACvZMG1M/g0KrPraG8HKtNP6lRs8XLwPF24hMbQewyD5n0ryxVY5vw1hdObWxXvit/bzdJZJMUEHpkzQ= Received: by 10.78.157.15 with SMTP id f15mr566203hue; Fri, 07 Jul 2006 01:36:53 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Fri, 7 Jul 2006 01:36:53 -0700 (PDT) Message-ID: Date: Fri, 7 Jul 2006 03:36:53 -0500 From: "Aaron Bono" To: "Phillip Smith" Subject: Re: Select Maths Cc: pgsql-sql@postgresql.org In-Reply-To: <012b01c6a192$1b827730$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_106923_11841667.1152261413241" References: <012b01c6a192$1b827730$9b0014ac@ITPhil> X-Google-Sender-Auth: fd5b44d21b3c2a73 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/84 X-Sequence-Number: 25658 ------=_Part_106923_11841667.1152261413241 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/7/06, Phillip Smith wrote: > > Same SELECT query as before, different area of it=85 I have a function th= at > calculates the recommended purchase order quantity for a stock item based > off various other values and functions: > > pqty(stock.code) AS "pqty" > > > > This needs to be rounded up / down to the nearest multiple of the *purcha= se > unit* quantity for that product =96 It's Friday afternoon and my head has > refused to help me work out the maths all afternoon! > > > > Example: > > Pqty =3D 60 > > Purchase Unit =3D 25 > > Pqty needs to be rounded down to 50. > > > > I guess I'm also asking if I should do this in the Pqty function or in th= e > SELECT query to optimize the result? > select 25 * round(cast(60 as double precision) / 25) ------=_Part_106923_11841667.1152261413241 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/7/06, Phillip Smith <phillips@weatherbeeta.com.au> wr= ote:

Same SELECT query as before, different area of it=85 I have a function that calculates the recommended purchase order quantity for a stock item based off various other values and functions:

pqty(stock.code) AS= "pqty"

This needs to be rounded up / down to the nearest multiple of the purchase unit quantity for that product =96 It's Friday afternoon and my head has refused to help me work out the maths all afternoon!

Example:

Pqty =3D 60

Purchase Unit =3D 25

Pqty needs to be rounded down to 50.<= /font>

I guess I'm also asking if I should do this in the Pqty function or in the SELECT query to optimize the result?=

select 25 * round= (cast(60 as double precision) / 25)

------=_Part_106923_11841667.1152261413241-- From phillips@weatherbeeta.com.au Mon Jul 10 07:22:54 2006 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 6D62F9FB257 for ; Mon, 10 Jul 2006 04:22:54 -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 13558-09 for ; Mon, 10 Jul 2006 04:22:42 -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 AAF589FB251 for ; Mon, 10 Jul 2006 04:22:40 -0300 (ADT) Received: (qmail 25962 invoked from network); 10 Jul 2006 07:22:37 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;f48cd69d1cb2e0694bd82413eca1bf56; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 10 Jul 2006 07:22:37 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d55f414 for pgsql-sql@postgresql.org; Mon, 10 Jul 2006 17:20:29 +1000 From: "Phillip Smith" To: Subject: Re: Select Maths Date: Mon, 10 Jul 2006 17:21:53 +1000 Message-ID: <000001c6a3f1$84cf1ed0$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C6A445.567D9FD0" 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-1152516157.491-25955-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.19 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/143 X-Sequence-Number: 25717 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C6A445.567D9FD0 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: 7bit 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. 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", Thanks again for all your help guys, -p -----Original Message----- From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono Sent: Friday, 7 July 2006 18:37 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Select Maths On 7/7/06, Phillip Smith wrote: Same SELECT query as before, different area of it. I have a function that calculates the recommended purchase order quantity for a stock item based off various other values and functions: pqty(stock.code) AS "pqty" This needs to be rounded up / down to the nearest multiple of the purchase unit quantity for that product - It's Friday afternoon and my head has refused to help me work out the maths all afternoon! Example: Pqty = 60 Purchase Unit = 25 Pqty needs to be rounded down to 50. I guess I'm also asking if I should do this in the Pqty function or in the SELECT query to optimize the result? select 25 * round(cast(60 as double precision) / 25) *******************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_0001_01C6A445.567D9FD0 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

&n= bsp; --^^^-- 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)

&n= bsp; --^^^-- 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

&n= bsp; --^^^-- Check that our suggested purchase qty is greater than our Usage Rate x = Creditor Review Cycle

END AS = "pqty",

<SNIP>

Thanks again for all your help = guys,

-p

-----Original Message-----
From: = aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On = Behalf Of Aaron Bono
Sent: Friday, 7 July 2006 18:37
To: Phillip Smith
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] Select = Maths

On 7/7/06, = Phillip Smith <phillips@weatherbeeta.com.au= > wrote:

=

Same SELECT query as before, = different area of it… I have a function that calculates the recommended purchase = order quantity for a stock item based off various other values and = functions:

pqty(stock.code) AS "pqty"

This needs to be rounded up / down = to the nearest multiple of the purchase unit quantity for that product = – It's Friday afternoon and my head has refused to help me work out the = maths all afternoon!

Example:

Pqty =3D 60

Purchase Unit =3D 25

Pqty needs to be rounded down to = 50.

I guess I'm also asking if I = should do this in the Pqty function or in the SELECT query to optimize the = result?

select 25 * round(cast(60 as double precision) / 25)

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

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-- From phillips@weatherbeeta.com.au Tue Jul 11 00:31:01 2006 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*******************

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-- From phillips@weatherbeeta.com.au Tue Jul 11 03:52:07 2006 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 C41019FB2E3 for ; Tue, 11 Jul 2006 00:52:07 -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 62982-01 for ; Tue, 11 Jul 2006 00:52:02 -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 B2B279FB2E2 for ; Tue, 11 Jul 2006 00:52:01 -0300 (ADT) Received: (qmail 30156 invoked from network); 11 Jul 2006 03:51:59 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;d093d42af5e71d4bb081b67c0b588da6; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 11 Jul 2006 03:51:59 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d560c7c for pgsql-sql@postgresql.org; Tue, 11 Jul 2006 13:50:06 +1000 From: "Phillip Smith" To: Subject: Re: Select Maths Date: Tue, 11 Jul 2006 13:51:18 +1000 Message-ID: <00d301c6a49d$448cb290$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00D4_01C6A4F1.1638C290" 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-1152589919.668-30151-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.10 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/170 X-Sequence-Number: 25744 This is a multi-part message in MIME format. ------=_NextPart_000_00D4_01C6A4F1.1638C290 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: 7bit It ties back to my other post about the "FLAGS" column - I need to be able to find out if the original pqty() calculation has needed to be modified. I guess what you're saying is to have 2 functions - one that calculates the figure I have at the moment, then a second to return the adjusted figure? Then I can use the first function when I'm working out what the flags need to be, then the second to give the actual adjusted figure.. -p -----Original Message----- From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron Bono Sent: Tuesday, 11 July 2006 13:42 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Select Maths 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 *******************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_00D4_01C6A4F1.1638C290 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

It ties back to my other post about = the “FLAGS” column – I need to be able to find out if the original pqty() = calculation has needed to be modified.

I guess what you’re saying is = to have 2 functions – one that calculates the figure I have at the moment, = then a second to return the adjusted figure?

Then I can use the first function = when I’m working out what the flags need to be, then the second to give the = actual adjusted figure….

-p

-----Original Message-----
From: = aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of Aaron = Bono
Sent: Tuesday, 11 July 2006 13:42
To: Phillip Smith
Cc: = pgsql-sql@postgresql.org
Subject: Re: [SQL] Select = Maths

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

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