Re: GROUP SUM with WHERE in REPORT

It's a feature... >From: John Prideaux <alastl@ix.netcom.com> >Date: Tue, 12 Nov 1996 13:56:23 -0600 >X-Informix-List-Id: <news.30370> > >For this example, assume that all variables are declared correctly >and sensibly and that this code exists in an AFTER GROUP clause of >a report. I have verified that at the point in time where the >GROUP SUM is executed that curr_year = 1995 and that rows with >rpt.data_year = 1995 have been passed to the report. > >for i = 1 to num_years > let curr_year = year_array[i] > let rpt.sub_line_year = curr_year > let turn_rec.begin_tons = (group sum(rpt.begin_tons) > where (rpt.data_year = curr_year)) >.... >end for > >This code places a null into turn_rec.begin_tons. >If I replace the last line with > where (rpt.data_year = 1995) >I get correct output. > >Is this a bug or a feature? I can restructure my report to get >the behavior I need, but I am curious. If you can find the c-code which processes the aggregates, you'll realise that it is some of the most inscrutable code known to mankind. It makes the Obfuscated C competition look like child's play, but it occupies far too much space to be a contender:-) Basically, each group aggregate such as "GROUP SUM(X) WHERE Y = Z" is evaluated once per call to OUTPUT TO REPORT. The evaluated value is then subbed into the loop. It isn't clear what value would be produced; it would depend on what curr_year was set to at the point in the code where the aggregate is evaluated. If my memory serves me right, then aggregates are processed immediately on entry to the report -- or maybe at some point where it is determined that this value needs to go in the same group aggregate as last time the report was called, or the aggregates have been reset to zero. Why you get a null, I'm not sure. I'd expect zeroes. I would expect trouble regardless, unless you were grouping by curr_year in a more significant group than the current group -- but you clearly are not doing that. Reports were not designed to handle what you are asking it to do. What you are asking is not unreasonable -- it just isn't something they are designed to handle. A warning would be a good idea, but it doesn't happen. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>