Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only