>>I have inheritedthe an application written in VFP5 that I am migrating to VFP9.
>>
>>There is a very complex view that in VFP9 complains that the group by is missing or invalid.
>>
>>In the VFP5 view, there is no group by clause. I understand basically what the error means, etc. but...
>>
>>In the past, to convert the SQL, I often created more than one query and combined them to get the same result.
>>
>>The issue I have this time is, this is a complex view and it is updateable. Any ideas on how to do it in one, keeping it as a single updateable view?
>>
>>i.e. how to add the necessary group by clause that would return the same result?
>>
>>TIA,
>>Mike
>
>Michael,
>
>Can you post the view definition? You would need to make an aggregate function for all fields, which are not included into Group By clause or you may need to include all fields into group by. I am not sure, though, how can you have updatable view with GROUP BY.


Here is the SQL from the view definition:

SELECT 	VAL(Customer.CODE) AS ValCode,Invhead.*, Ordhead.location, Ordhead.entered,	 ;
	Ordhead.priorityorder,Ordhead.customerpo,Ordhead.daterequired,Ordhead.comm1, ;
	Ordhead.comm2, Ordhead.comm3, Ordhead.terms, Customer.NAME,Customer.CODE, Customer.creditlmt, ;
	Customer.einnumber, Customer.specialdisc, Customer.dollarsuse, Ordhead.customerid, ;
	Customer.Country AS CustCountry, ProdQty.TotOrdered,ProdQty.TotAllocated, ;
	ProdQty.TotShipped,.F. AS Ok,RdyToShip.RdyToShip,Shipped.Shipped, ;
	ProdQty.SpecialOrd,ALLTRIM(STR(Ordhead.OrderId))+IIF(Ordhead.SBO,"B"," ") AS DispOrdNo, ;
	Customer.OnHold	;
	FROM frantisi!Invhead, frantisi!Ordhead, frantisi!Customer, frantisi!ProdQty, ;
	frantisi!RdyToShip,frantisi!Shipped	 ;
	WHERE Ordhead.customerid = Customer.customerid	;
	AND Invhead.OrderId = Ordhead.OrderId ;
	AND	Invhead.OrderId	= Shipped.OrderId ;
	AND	(((EMPTY(?ldDateReqd)) OR (Ordhead.daterequired <= ?ldDateReqd)) ;
	OR NOT EMPTY(Invhead.Picked) ) AND ((?lnLocn= 0) ;
	OR (Ordhead.location = ?lnLocn)) AND EMPTY(DispatchDate) ;
	ORDER BY 1,Invhead.InvoiceNo

------------
The Key Field is: InvHead.InvoiceNo
Updateable fields are:
From InvHead; InvoiceNo,InvoiceDate,DispatchDate,Discount,Tax,Freight,Brokerage,ShipVia,FOBPoint,Pi cked,Currency,Posted
From OrdHead; Location,Entered,PriorityOreder,CustomerPO,DateRequired,Comm1,Comm2,Comm3
From Customer; Name,Code,CreditLmt,EINNumber,SpecialDisc,DollarUse,OnHold
From RdyToShip; RdyToShip
From Shipped; Shipped
----------------------------
I don't understand how this could have worked.

TIA,
Mike