Albert, Creating a view will not help either because in my same data sum of sales is zero in one case, sum of cost is zero in one case and sum of profit is zero in one case!
So one of the sums will be zero and if that one is the denominator then you get the error messages unless the numerator is zero! Thank for you thoughts! I tried your example and still get the zero divide error! Thanks I'm walking away for a few days and I our get back to a work around! Best regards, Oma -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Albert Berry Sent: Thursday, January 09, 2003 8:58 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Just some I wanted to share! OOPS Oma - Create a view that calculates all but the percentage, and use it as the source for the percentage calculation. That will get around the internal troubles. CREATE VIEW vwFirstView AS + select custno, sum(monsales) as Sales,sum(moncost) as + Cost,(sum(monsales)-sum(moncost)) as Profit + from test1 group by custno SELECT CustNo=8, Sales=8, Cost=8, Profit=8, + (ifeq(Sales,0,0,(Profit/Sales))) as Percent + FROM vwFirstView > "Sami Aaron" <[EMAIL PROTECTED]> wrote: >Oma - > >I'll gladly take your $10 for Project Bundle-Up ... > >And here's a thought about your statement in general - and I may be way off >base, but ... > >I think you have a logic flaw. �You're basing the IFEQ portion on the result >of a value that can only be evaluated AFTER the GROUP BY portion has been >run, but you are attempting to display the results BEFORE THE group by. > >Re-phrased - my understanding of the SELECT ... GROUP BY syntax requires >that the values that are collected to the left of the GROUP by are based on >a row-by-row evaluation of the data in the table. �By attempting to place >the IFEQ in this group, you are trying to force the results of the GROUP BY >to be evaluated on a row-by-row basis - but it's impossible until it has >completed all the rows in the group. > >Anyway - that's all the brain power I have left for the morning ... > >Sami > >----------------------------------------------------------- >Sami Aaron >Software Management Specialists >19312 W 63rd Terr >Shawnee KS �66218 >913-915-1971 >http://www.softwaremgmt.com > > > >----- Original Message ----- >From: "Oma Cox" <[EMAIL PROTECTED]> >To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> >Sent: Thursday, January 09, 2003 6:58 AM >Subject: [RBASE-L] - Re: Just some I wanted to share! OOPS > > >Ok I posted my results before I did enough testing (some times >excitement of getting it to work blinds you from the truth!)! >ASSUMED the answer was correct because the error went away! >After checking further there is no way to trap for a division by zero >I'll put $10 bucks up for this DUH Sami! >You can get profit percentage two ways >Sales - cost = profit > >Profit/sales = % >Or >(Sales/profit)^-1 = % > >The IFEQ function doesn't not handle this issue well! > >(ifeq(sum(monsales),0,0,((sum(monsales)-sum(moncost))/(sum(monsales)) > >Someone please check my logic on this! > >If sum(monsales) = 0 then >Set value = 0 >Else >Set Value = ((sum(monsales)-sum(moncost))/(sum(monsales)) >endif > >The expression ((sum(monsales)-sum(moncost))/(sum(monsales)) is >evaluated before the condition is checked! So if you have a denominator >in your formula that equals zero (through a sum) then you'll get a >Divide by Zero error! > >Is there away round this! Someone please set me straight! > >The expressions within the IFEQ function should be evaluated upon >satisfaction of the condition not before! > >The select statement can have monsales equal zero due to credits applied >to that customer and profit (monsales-moncost) can equal zero too (very >rare) so it doesn't matter which denominator is used or method noted >above to calculate profit percentage! You will still get an error >message some where down the line. > >Am I over reacting about this! > >I know that you could add a column to the table to compute the profit >percentage but if you are using the sum you can not sum or avg the >precent oolumn! So this option won't work using the select? > >Which I've done in my example below! > > >Best regards, > >Oma > > >Example > >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 ON >CREATE SCHEMA AUTHOR test1 public >CREATE TEMPORARY TABLE `test1` �+ >(`custno` TEXT � �(8) , �+ > `monsales` CURRENCY , �+ > `moncost` CURRENCY , �+ > `monprofit`= + > (monsales-moncost) CURRENCY , �+ > `monprecent`= + > (monprofit/monsales) DOUBLE � ) >SET CASE OFF >SET AUTOSKIP ON >SET REVERSE ON >SET BELL OFF >SET NULL '-0- ' >SET DATE YEAR 30 >SET DATE CENTURY 19 >SET DATE SEQUENCE MMDDYYYY >SET TIME SEQUENCE HHMMSS >SET TOLERANCE 0. >SET ZERO ON >LOAD `test1` >NONUM >'100',100.00,50.00 >'100',100.00,100.00 >'100',-200.00,-150.00 >'120',100.00,60.00 >'120',-80.00,-60.00 >'130',134.00,54.00 >'130',-134.00,48.00 >'140',100.00,50.00 >'140',150.00,45.00 >END >SET DATE FORMAT 'MM/DD/YYYY' >SET TIME FORMAT 'HH:MM' >SET DATE SEQUENCE MMDDYY >SET TIME SEQUENCE HHMMSS >COMMENT ON TABLE `test1` IS + >'Testing of Divide by Zero' > >-- get divided by zero error >select custno=8 as CustNo, sum(monsales)=8 as Sales,sum(moncost)=8 as >Cost,(sum(monsales)-sum(moncost))=8 as Profit, >(ifeq(sum(monsales),0,0,((sum(monsales)-sum(moncost))/(sum(monsales)) >)))=8 as Precent from test1 group by custno > > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
