Fabio --
Thanks very much for your detailed explanation. That helps very much.
Jim
>>Fabio --
>>
>>Yes, that seems to work all right.
>>
>>Just out of curiousity, I don't understand the use "Orders" in the next to last line:
Orders GROUP BY OrdNbr) TGROUP
Curiously, it seems like any old name will work there, as long as something goes there. What's going on there?
>>
>>Thanks,
>>Jim
>>
>
>When a subquery is used it needs to give a name to the intermediary Table.
>In this case the subquery (SELECT OrdNbr,RECNO () rn, AMT FROM Orders)
>it is necessary only because VFP doesn't support RECNO() in the SQL commands
>(a very serious lack).
>If VFP supported (and it optimized) the use of RECNO(), then this type of
>queries would be simple and extremely faster
>(3-10x in comparison to one who uses indexes)
>In our case it would be enough:
>
>SELECT TDATA.*,TGROUP.TOTAMT ;
> FROM Orders TDATA;
> JOIN (SELECT MAX(RECNO()) rn ,SUM(AMT) TOTAMT FROM Orders GROUP BY OrdNbr) TGROUP;
> ON TGROUP.rn=RECNO('TDATA')
>
>and you see that Orders is in the same position!
>
>In concrete, for an efficient solution (list is in order):
>- or you use Set 70 (you see my first message)
>- or you look for a primary key (you see my first message)
>- or you use the names with
>
>SELECT OrdNbr,MAX(orderInfo1),MAX(orderInfo2)..,SUM(AMT) FROM Orders GROUP BY OrdNbr
>
>Other solutions don't exist without normalizing Orders.
Jim Nelson
Newbury Park, CA