> 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!
This has come up before. While I think Sami is correct in how group by works, even if you remove "group by" from your example and refer only to CustNo = 100, you will get the error... even though the if:then structure should have avoided it. You should not get a devide by 0 error if MonCost = 0 ... 0/anything = 0. My first thought is to build a view using group by (but not the profit calc) and then do two selects against the view... one where MonSales = 0 and one where not. But there should be something more elegant than that. Ben Petersen On 9 Jan 2003, at 6:58, Oma Cox wrote: > 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 > > > > > >
