Today I was working on a select statement with an embedded format and IFEQ function.
Original Select: select custname=30 as CustName,sum(monsales)=13=S as Sales,sum(moncost)=12=S as Cost,(sum(monsales)-sum(moncost))=10 as Profit,(format((ifeq(sum(monsales),0,0,((sum(monsales)-sum(moncost))/sum (monsales))*100)),'99.0'))=8 as ProfitP from tsalestotals group by custname where (bdate between .vbdate and .vedate and warehno = .vwarehno) order by sales=d Revised Select: select custname=30 as CustName,sum(monsales)=13=S as Sales,sum(moncost)=12=S as Cost,(sum(monsales)-sum(moncost))=10 as Profit,(format((ifeq(sum(monsales),0,0,((sum(monsales)/sum(moncost))^-1) *100)),'99.0'))=8 as ProfitP from tsalestotals group by custname where (bdate between .vbdate and .vedate and warehno = .vwarehno) order by sales=d The original select will work fine until sum(monsales) = 0 then I get a divide by zero error! The revised select will work all the time because eliminating the possibility of dividing being done by zero. This one was fun because it occurred only on 1 of 20 reports where used. So the ifeq function either select zero for sum(monsales) equals zero and if not then the invert (^-1) of sum(monsales)/sum(moncost) times 100 will give you the percentage of profit! Just wanted to share this there is no problem here but just an example of what will work 99% of the time and one that will work 100%. Best regards, Oma 17:57 CST
