Tweet
It has something to do with the rules for numerical precision. Try recasting the sums themselves: dec( dec( sum( g1gpro ),15,5 ) / dec( sum( g1gall ),15,5),7,5)
-
-
Embedded SQL and Job Logs
That worked great. I am not completely sure why but at this point I don't care. thanks Bill -
Embedded SQL and Job Logs
This is actually a 2 parter. 1 - Is there a way to prevent massive job logs when executing a program with embedded SQL? 2 - this sql statement does not produce any results for the MARGIN field select g1compno,g1group, g1region,g1division,g1district, sum(g1gall) as Gallons, sum(g1gpro) as Profit, dec(sum(g1gpro) / sum(g1gall),7,5) as Margin from gast/gpsp0040 where g1stor = 0 and g1market = ' ' and g1trn@ = '2004-10-26' and g1compno <> 0 group by g1compno,g1group,g1region,g1division,g1district order by g1compno,g1group,g1region,g1division,g1district yet this one does select g1compno,g1group, g1region,g1division,g1district, sum(g1gall) as Gallons, sum(g1gpro) as Profit, dec(sum(g1gpro) + sum(g1gall),7,5) as Margin from gast/gpsp0040 where g1stor = 0 and g1market = ' ' and g1trn@ = '2004-10-26' and g1compno <> 0 group by g1compno,g1group,g1region,g1division,g1district order by g1compno,g1group,g1region,g1division,g1district the only difference between them is the '/' and '+' I can replace the / with +,- or * and I get results in the MARGIN but not when I use the / The frst record returned has Gallons of 3,777,347.26 and Profit of 256,674.16 which should give me a Margin of .06795 but I get 0 If I do the calculation on detail without the sum I get the proper results at the detail level, but I really want them at teh summary level. What have I done wrong. Just an FYI for those of you complaining about Gasoline prices. The biggest profit maker on a gallon of fuel is the government about .40 to .50 cents per gallon in the SE. I don't know what it is up north or on the left coast. TIA BillComment
Comment