Fabio --

I guess there were two pieces of relevant information I failed to include:

(1) These are free-standing tables.
(2) They're not mine to modify.

Thus, I have to use them with the structure they have now.

I do appreciate your suggestion, however, of temporarily moving to the older SQL behavior, if all else fails. Of course, what I'm trying to do is to move everything to version 90, and would assume that problems such as this are solvable there.

Thanks,

Jim

>>In moving from SET EngineBehavior 70 to SET EngineBehavior 90 , I've encountered the same difficulties mentioned numerous times in this forum regarding the special attention necessary for the GROUP BY clause. I now understand the changes that are necessary when the old code fails, and have been happily making them as necessary.
>>
>>However, I do have some instances where the old behavior worked very nicely, and I don't see an easy solution using the new behavior:
>>

SELECT *, SUM(AMT) AS TOTAMT FROM Orders GROUP BY OrdNbr

>>The table "Orders", unfortunately, contains both parent records and child records. This particular selection would give me one record for each OrdNbr and all of the original fields from "Orders", so that I would, in effect, be creating the parent records (with the fields for what should have been child records containing irrelevant information.)
>>
>>So, is it possible to achieve the same effect using the new behavior without enumerating all the fields in the GROUP BY clause? (I can do it by enumerating the fields, but I certainly would hope not to have to do so).


>>
>>Thanks
>
>Require a PK or candidate key
>

>* supposing OrdNbr,ChildRowNbr defines a record univocal
>
>SELECT Orders.*,TGROUP.TOTAMT;
>(SELECT OrdNbr,MAX(ChildRowNbr) ChildRowNbr,SUM(AMT) TOTAMT FROM Orders GROUP BY OrdNbr) TGROUP;
>JOIN Orders;
>ON Orders.OrdNbr=TGROUP.OrdNbr AND Orders.ChildRowNbr=TGROUP.ChildRowNbr
>

>
>or
>

>TRY
> SET EngineBehavior 70
> SELECT *, SUM(AMT) AS TOTAMT FROM Orders GROUP BY OrdNbr
>FINALLY
> SET EngineBehavior 90
>ENDTRY
>