G'day Bill,
Thanks for the suggestion on combining the commands.
What I forgot to mention is that as I stepped through
the debugger I confirmed that each of the v_
variables has a valid currency value at the point of the:
SET VAR vSumPromoGI CURRENCY = +
(.v_1 + .v_2 + .v_3 + .v_4 + .v_5 + .v_6 + .v_7 + .v_8 + .v_9 + .v_10)
command.
That's why the failure to sum them has me tossed.
I just reran the code and the first time I run it I get an
"Invalid date value" message - only on performing the calc for v_2!
Why only on that one and not on the others, given the same where
clause has me tossed as the dates checked as valid using TRACE.
Anyway the problem appears to be solved as upgrading my code
as per your suggestion to what follows has yet to glitch.
$COMMAND
CalSumGI
-- Sums Deposits received for where clause
-- Called by:
-- RptsMenu in PromCmpn.apx
*( Requires var
vWhere
Returns var
vSumPromoGI
)
-- Created by Tom Grimshaw 25-04-2002
-- Modification history
-- 15-05-2002 tlg Combined SELECTS to one
--
SET VAR v1 CURRENCY = 0.00
SET VAR v2 CURRENCY = 0.00
SET VAR v3 CURRENCY = 0.00
SET VAR v4 CURRENCY = 0.00
SET VAR v5 CURRENCY = 0.00
SET VAR v6 CURRENCY = 0.00
SET VAR v7 CURRENCY = 0.00
SET VAR v8 CURRENCY = 0.00
SET VAR v9 CURRENCY = 0.00
SET VAR v10 CURRENCY = 0.00
SELECT SUM(GI_0_30_Days),SUM(GI_31_61_Days),SUM(GI_62_92_Days), +
SUM(GI_93_123_Days),SUM(GI_124_153_Days),SUM(GI_154_183_Days), +
SUM(GI_184_275_Days),SUM(GI_276_365_Days),SUM(GI_366_732_Days), +
SUM(GI_Over732_Days) INTO +
v1 IND vi1, +
v2 IND vi2, +
v3 IND vi3, +
v4 IND vi4, +
v5 IND vi5, +
v6 IND vi6, +
v7 IND vi7, +
v8 IND vi8, +
v9 IND vi9, +
v10 IND vi10 +
FROM Promotions +
&vWhere
SET VAR vSumPromoGI CURRENCY = +
(.v1 + .v2 + .v3 + .v4 + .v5 + .v6 + .v7 + .v8 + .v9 + .v10)
CLEAR VAR v1,v2,v3,v4,v5,v6,v7,v8,v9,v10
RETURN
At 23:09 14/05/02 -0500, you wrote:
>Test each variable for null, and set the nulls to zero before you add
>them together. If you sum things, and one of the items is a null, your
>result should be a null (unless you override the SQL default and SET
>ZERO ON).
>
>Meanwhile, you could do all 10 of your sums in a single command,
>and not hit the database 10 times, but that's another issue. Since the
>FROM And WHERE clauses are all the same, you should combine
>them:
>
>SELECT +
> SUM(GI_0_30_Days), +
> SUM(GI_31_61_Days), +
> etc.
> INTO v_1 IND vi1, v_2 IND vi2, etc. +
> FROM Promotions +
> &vWhere
>
>On Wed, 15 May 2002 11:23:22 -0700, Tom Grimshaw wrote:
>
> >G'day,
> >
> >I have a problem summing variables for a report. The weird thing
> >is that sometimes she works, sometimes she don't. Sometimes the
>var
> >vSumPromoGI is without one of the constituents. Which one it is
> >missing varies from one running of the code to another.
> >
> >Is there something I am doing wrong or should I report this to RDCC>
> >
> >Here is the code:
> >
> >$COMMAND
> >CalSumGI
> >-- Sums Deposits received for where clause
> >-- Called by:
> >-- RptsMenu in PromCmpn.apx
> >*( Requires var
> >vWhere
> > Returns var
> >vSumPromoGI
> >)
> >-- Created by Tom Grimshaw 25-04-2002
> >-- Modification history
> >--
> >
> >SET VAR v_1 CURRENCY = 0.00
> >SET VAR v_2 CURRENCY = 0.00
> >SET VAR v_3 CURRENCY = 0.00
> >SET VAR v_4 CURRENCY = 0.00
> >SET VAR v_5 CURRENCY = 0.00
> >SET VAR v_6 CURRENCY = 0.00
> >SET VAR v_7 CURRENCY = 0.00
> >SET VAR v_8 CURRENCY = 0.00
> >SET VAR v_9 CURRENCY = 0.00
> >SET VAR v_10 CURRENCY = 0.00
> >SELECT SUM(GI_0_30_Days) INTO +
> > v_1 IND vi1 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_31_61_Days) INTO +
> > v_2 IND vi2 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_62_92_Days) INTO +
> > v_3 IND vi3 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_93_123_Days) INTO +
> > v_4 IND vi4 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_124_153_Days) INTO +
> > v_5 IND vi5 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_154_183_Days) INTO +
> > v_6 IND vi6 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_184_275_Days) INTO +
> > v_7 IND vi7 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_276_365_Days) INTO +
> > v_8 IND vi8 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_366_732_Days) INTO +
> > v_9 IND vi9 +
> > FROM Promotions +
> > &vWhere
> >SELECT SUM(GI_Over732_Days) INTO +
> > v_10 IND vi10 +
> > FROM Promotions +
> > &vWhere
> >SET VAR vSumPromoGI CURRENCY = +
> >(.v_1 + .v_2 + .v_3 + .v_4 + .v_5 + .v_6 + .v_7 + .v_8 + .v_9 + .v_10)
> >CLEAR VAR v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8,v_9,v_10
> >RETURN
> >
> >Any help on this would, as always, be greatly appreciated.
> >
> >Warmest regards,
> >
> >
> >Tom Grimshaw
> >coy: Just For You Software
> >tel: 612 9552 3311
> >fax: 612 9566 2164
> >mobile: 0414 675 903
> >
> >post: PO Box 470 Glebe NSW 2037 Australia
> >street: 3/66 Wentworth Park Rd Glebe NSW 2037
> >
> >email: [EMAIL PROTECTED]
> >web: www.just4usoftware.com.au
> >
> >"... the control of impulse -- is the first principle of civilization."--
> >Will Durant,
> >Pulitzer Prize winning philosopher, writer and historian
> >
> >the most needed product in the world can be found at
> >www.thewaytohappiness.org
> >
> >This email and any files transmitted with it are confidential to the
> >intended recipient and may be privileged. If you have received this
>email
> >inadvertently or you are not the intended recipient, you may not
> >disseminate, distribute, copy or in any way rely on it. Further, you
>should
> >notify the sender immediately and delete the email from your
>computer.
> >Whilst we have taken precautions to alert us to the presence of
>computer
> >viruses, we cannot guarantee that this email and any files transmitted
>with
> >it are free from such viruses.
> >
> >================================================
> >TO SEE MESSAGE POSTING GUIDELINES:
> >Send a plain text email to [EMAIL PROTECTED]
> >In the message body, put just two words: INTRO rbase-l
> >================================================
> >TO UNSUBSCRIBE: send a plain text email to
>[EMAIL PROTECTED]
> >In the message body, put just two words: UNSUBSCRIBE rbase-l
> >================================================
> >TO SEARCH ARCHIVES:
> >http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
>
>
>
>
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
"... the control of impulse -- is the first principle of civilization."--
Will Durant,
Pulitzer Prize winning philosopher, writer and historian
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the
intended recipient and may be privileged. If you have received this email
inadvertently or you are not the intended recipient, you may not
disseminate, distribute, copy or in any way rely on it. Further, you should
notify the sender immediately and delete the email from your computer.
Whilst we have taken precautions to alert us to the presence of computer
viruses, we cannot guarantee that this email and any files transmitted with
it are free from such viruses.
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
