Hi Randy

There seems to be a lot of people having trouble with GROUP BY being invalid. When a new feature comes out, re-examine old ideas. I like to think when something is painful it probably means there has to be a better way to approach it. I believe the problem is one of perspective. Do not denormalize all joined tables into a flat file structure and then do the summing, because that will guarantee you'll have many columns in the GROUP BY.

I found it easier to do the summing first and then join that result into the rest of the query. Long ago, I stopped using the DataEnvironment because I kept running into queries like this which would have been better done as two queries. The DE made it hard to add a new cursor, and place it before the existing cursors. It seemed really dumb to remove all the cursors from the DE and re-add them.

You're doing a sum of COr_Amount from CertificateOrder and JOINING all the other tables for purposes of a report, right? Invert that thinking! Select from the "other" tables and use a subselect for the SUM.

You'd do it with something like:

INNER JOIN ;
(select sum(COr_Amount) as COr_Amount_Sum;
from ST!CertificateOrder ;
WHERE COr_OrCFK = '00054' ;
GROUP BY COr_OrCFK) COr_Amount_Sum ;
ON CSR_PK = COr_CSRFK