Re: Informix SQL - ACE question....
I think I get what you mean.
This is what I'd do ...
select distinct <some-columns>, 0 col_x, 0 col_y, 0 col_z
from <table>
unionselect <some-columns>, col_x, col_y, col_z
from <table>
into temp t_final with no log;
select <some-columns>,
sum(col_x) x, sum(col_y) y, sum(col_z) z, sum(col_x + col_y + col_z) xyz
from t_final
group by <some-columns>
order by <whatever>
end
format
...
after group of <whatever>
print group total of xyz using
hth
>>> "Mark Caton" <caton@qx.net> 04/07/00 02:55am >>>
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