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/
