I do appreciate all the input regarding this issue. Examples help me "SEE" the difference between the two settings, so your explanation helped, Bill.
Since I do use averages periodically, I have to take this into account. Thanks again. Jim ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Thursday, June 24, 2010 3:05 PM To: RBASE-L Mailing List 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.
