This is legacy code I inherited from someone else's VFP 5 application that I am upgrading to VFP 9. The problem is this is a view definition. I really don't want to break it into 2. Any more ideas? (I also agree about the where clause... I guess a couple of ineer joins would be a good idea as well...)

Thanks,
Mike


>Hi Michael
>
>I've been stressing this at work, but it falls on deaf or dumb ears: Programming is between input and output. Tell me the input and the desired output and I'll program anything. Besides which, if we have sample input and desired output the program can be tested.
>
>A hint for you though. You are summing up invprod. Everything else is a lookup to the invprod record - invhead - ordhead - customer. To me that suggests summing invprod to one resulting cursor and then in a second query join the other tables to that result.
>
>It's easier to follow.
>
>I also find it extremely helpful to only use the where to filter not to join. ;)
>
>>Can people help on how to change this query to run in VFP9 from VFP7?
>>
>>Group by is invalid is the error
>>
>>SELECT Invhead.orderid, Invhead.invoiceno, Invhead.invoicedate,;
>>Customer.Code as Code, Invhead.freight, Invhead.brokerage, ;
>>Ordhead.entered, Customer.name AS custname, Ordhead.location, ;
>>Invprod.invoiceqty, SUM(invoiceqty*sellprice) as Value, ;
>>SUM(invprod.invoiceqty*ordprod.sellprice)*(InvHead.Discount/100) as Discount, ;
>>(SUM(invprod.invoiceqty*ordprod.sellprice)-(SUM(invprod.invoiceqty*ordprod.sellprice)*(InvHead.Discount/100)) + InvHead.Freight)*(InvHead.Tax/100) as Tax ;
>>FROM invhead, invprod, ordhead, ordprod, customer ;
>>WHERE Invhead.orderid = Ordhead.orderid ;
>>AND Invhead.invoiceno = Invprod.invoiceno ;
>>AND Ordhead.customerid = Customer.customerid ;
>>AND Ordhead.orderid = Ordprod.orderid ;
>>AND Invprod.productid = Ordprod.productid ;
>>GROUP BY Invhead.invoiceno ;
>>INTO CURSOR salejour
>>
>>
>>Needless to say, adding all fields to the group by gives more records...
>>
>>Help!
>>
>>TIA,
>>Mike