>>The honor of your assistance is requested. I've spent too many hours looking at this SQL-select statement trying to figure out why "The GROUP BY clause is missing or invalid". In the first part, I've added the field type/size at the end of the line. This is generating a cursor for a report.
>
>Update: Typos corrected in statement below. SET ENGINEBEHAVIOR is 90. I started reading on page 56 of your book, and did not find anything applicable in that chapter. Maybe I need to do two SELECTs to drive the report? The Brk_* and CtZ_* fields are only used in a group header.
>
>SELECT Brk_Name, Brk_Address1, Brk_Address2 , ;
>> CtZ_CityName, CtZ_State, CtZ_PostalCode , ;
>> Brk_Contact, Brk_SingleMultiple , ;
>> Brk_ShippingRate, Brk_ShippingRules , ;
>> CtV_DisplayOrder, CSR_DisplayOrder , ;
>> CtV_StoreName, CSR_CertificateAmount , ;
>> SUM(COr_Amount) AS COr_Amount_SUM , ;
>> CSR_Discount, CSR_Percent, CSR_OrgPercent ;
>>FROM ST!CertificateOrder ;
>> JOIN ST!CertificateSizeRate ON COr_CSRFK==CSR_PK ;
>> JOIN ST!CertificateVoucher ON CSR_CtVFK==CtV_PK ;
>> JOIN ST!Broker ON CSR_BrkFK==Brk_PK ;
>> LEFT JOIN ST!CityZip ON Brk_CtZfk==CtZ_PK ;
>>WHERE COr_OrCFK = '00054' ;
>>ORDER BY Brk_Name, CtV_DisplayOrder, CSR_DisplayOrder ;
>>GROUP BY Brk_SingleMultiple, Brk_Name , ;
>> CtV_DisplayOrder, CSR_DisplayOrder , ;
>> CtV_StoreName, CSR_CertificateAmount , ;
>> CSR_Discount, CSR_Percent, CSR_OrgPercent , ;
>> Brk_Address1, Brk_Address2 , ;
>> CtZ_CityName ,CtZ_State, CtZ_PostalCode, Brk_ShippingRate ;
>>INTO CURSOR rc_OrderTabReport
>>It works as expected without the SUM() and GROUP BY. I've made sure all the non-grouped fields are included and that there are no memo fields in the GROUP BY. Can you see what I've missed?
>>
As the others said, the memo fields are the problem. One way to resolve this is to use a nested SELECT. Have a SELECT that does the aggregation you need with the minimum fields and use it as a derived table in an outer SELECT.
Tamar
