The other consideration for SET ZERO ON is for reports and variables in the report. I have found it advantageous to have it set ON as I a run a report. Otherwise, you may not complete the calculation. In that case I generally SET ZERO ON just before the print command.
Gary ________________________________ From: Bill Downall <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Thu, June 24, 2010 3:05:17 PM Subject: [RBASE-L] - Re: CFG file SET ZERO ON can affect AVG(), STDEV() and VARIANCE computations or aggregate SQL functions. For example, when zero is set on the SQL AVG() function will divide by the count of all rows, not by the count of all non-null rows. Note that these are with an expression, since adjscore is an integer, and I wanted a result that was not rounded to integer. R>set zero off R>sel count (*), count (float(adjscore)), sum (adjscore), avg(float(adjscore)) from rounds count (*) count (flo sum (adjsc avg (float(adjs ---------- ---------- ---------- --------------- 3065 3061 268798 87.813786344332 R>set zero on R>sel count (*), count (float(adjscore)), sum (adjscore), avg(float(adjscore)) from rounds count (*) count (flo sum (adjsc avg (float(adjs ---------- ---------- ---------- --------------- 3065 3065 268798 87.699184339315 R>set zero off R>compute all (float(adjscore)) from rounds (float(adjscore)) Count = 3061 Rows = 3065 Minimum = 19. Maximum = 132. Sum = 268798. Average = 87.8137863443319 Std Dev = 19.385290716136 Variance = 375.789496149108 R>set zero on R>compute all (float(adjscore)) from rounds (float(adjscore)) Count = 3065 Rows = 3065 Minimum = 0. Maximum = 132. Sum = 268798. Average = 87.6991843393148 Std Dev = 19.6304022346265 Variance = 385.352691893227 On Thu, Jun 24, 2010 at 3:42 PM, Kenny Camp <[email protected]> wrote: If you use computed columns where one of the columns in the formula may be >null, the set zero on is very handy. You get the correct answer. > >Or else write a more SQL standard formula testing for nulls. > >Kenny > > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle >Sent: Thursday, June 24, 2010 1:59 PM >To: RBASE-L Mailing List >Subject: [RBASE-L] - Re: CFG file > > >RAZZAK, > >I NOTICED WHEN YOU USE THE ZERO ON SETTINGS YOU ALSO SET THE NULL TO ' ' >IF WE ARE USED TO THE NULL SETTINGS TO BE -0- WOULD IT BE BETTER TO USE >THE ZERO OFF SETTINGS? > >I hope you will forgive what many I am sure consider basic questions. >I just want to make sure I do not run into the situation you referred to >below of messing with my routines. > >I always thought our settings were ZERO OFF. It is only because of the >recent problems in the past few days that I even noticed it being ON. > > >So since we have been using the NULL -0- all this time thinking the ZERO >setting was OFF, would changing it as you show below create problems in >your opinion? I do not have a preference, I just want guidance here. > >I might add that since we started using 7.6 a few years ago, this is >only the second time I have run into this particular problem. > >Jim > > > >-----Original Message----- >From: [email protected] [mailto:[email protected]] On Behalf Of A. >Razzak Memon >Sent: Thursday, June 24, 2010 12:57 PM >To: RBASE-L Mailing List >Subject: [RBASE-L] - Re: CFG file > >At 12:19 PM 6/24/2010, Jim Belisle wrote: > >>I do use a form instead of an application, mostly because Razzak >>helped me understand some of the 7.6 functions by implementing >>them into a form. I do have some ON CONNECT options, just not >>ALL the settings. > >Jim, > >Mucking with database SETtings and then wondering why some routines >are not working is pretty hard to keep track of. However, you may >adapt a routine in startup file that will always ensure consistent >results when connecting to your specific database(s). > >Here is a code that you can use in your application startup file. > >Make sure to update anything specific to your database environment. > > >-- Start here >-- Start Fresh > CLEAR ALL VARIABLES >LABEL StartFresh > DISCONNECT > SET QUOTES=NULL > SET QUOTES=' > SET DELIMIT=NULL > SET DELIMIT=',' > SET LINEEND=NULL > SET LINEEND='^' > SET SEMI=NULL > SET SEMI=';' > SET PLUS=NULL > SET PLUS='+' > SET SINGLE=NULL > SET SINGLE='_' > SET MANY=NULL > SET MANY='%' > SET IDQUOTES=NULL > SET IDQUOTES='`' > SET CURRENCY '$' PREF 2 B > DISCONNECT > SET STATICDB OFF > SET ROWLOCKS ON > SET FASTLOCK OFF > SET TIMEOUT 120 > SET FEEDBACK OFF >LABEL Start > CLS > CONNECT mydbname IDENTIFIED BY NONE > -- Enforce Default Settings > SET QUOTES=' > SET DELIMIT=',' > SET LINEEND='^' > SET SEMI=';' > SET PLUS='+' > SET SINGLE='_' > SET MANY='%' > SET IDQUOTES='`' > SET CURRENCY '$' PREF 2 B > SET NULL ' ' > SET ZERO ON > SET DATE FORMAT MM/DD/YYYY > SET DATE SEQUENCE MMDDYY > SET DATE YEAR 30 > SET DATE CENTURY 19 > CLS > EDIT USING ApplicationMainMenu > RETURN >-- End here > >You may also save this routine as RBASE.DAT file, if you wish. > >Hope it helps! > > >Very Best R:egards, > >Razzak. > > >
