Informix SQL - ACE question....

I am working on an ACE report in Informix SQL and have hit a wall... On each row I'm outputing three columns of data each from a temp table and a fourth column which is the sum of the first three. Because one or more of the first three may be NULL I had to assign a variable for each and assign it the value "0" when NULL to maintain an appropriate sum in the fourth column. So far so good. I then have an "after group of" statement that attempts to sum the fourth column from above like so: after group of * print column 15, group total of * using "$$$$,$$$,$$$.&&" the results of this approach have not been good- the totals are not calculating correctly. The manual says about ACE aggregates such as "total": "Aggregates produce unpredictable results when expr1 or expr2 contains user-defined variables." Of course I am using variables in my "group total of" statement. I can't seem to avoid their use to get the other columns to sum correctly. Any ideas? Mark Caton

You can put a where clause on aggregate functions, IIRC PRINT TOTAL (col1 + col2) WHERE col1 IS NOT NULL AND col2 IS NOT NULL RTFM for exact syntax. HTH -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Mark Caton wrote in message ... >I am working on an ACE report in Informix SQL and have hit a wall... > >On each row I'm outputing three columns of data each from a temp table and a >fourth column which is the sum of the first three. Because one or more of >the first three may be NULL I had to assign a variable for each and assign >it the value "0" when NULL to maintain an appropriate sum in the fourth >column. So far so good. I then have an "after group of" statement that >attempts to sum the fourth column from above like so: > >after group of * >print column 15, group total of * using "$$$$,$$$,$$$.&&" > >the results of this approach have not been good- the totals are not >calculating correctly. > >The manual says about ACE aggregates such as "total": "Aggregates produce >unpredictable results when expr1 or expr2 contains user-defined variables." > >Of course I am using variables in my "group total of" statement. I can't >seem to avoid their use to get the other columns to sum correctly. > >Any ideas? > >Mark Caton > >