>>>>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
>
> Are 'RdyToShip' and 'ProdQty' tables or views/cursors?

They are both views:
RdyToShip is not updateable, therefore I do not understand why that field is updatable in the previous view defn.
(It consists of 2 fields - invoiceNo and a sum(invoiceqty) as rdytoship... from a table called invprod)

ProdQty is not updatable either, but it has the group by issue as the first query...
i.e.
Select orderid,SUM(TotOrdered) as TotOrdered,;
SUM(TotAllocated) as TotAllocated,Sum(TotShipped) as TotShipped,;
SizeQty.SpecialOrd ;
From SizeQty ;
Group by orderid

SizeQty is another view that is not updatable either, but has the group by issue as above...
i.e.
Select Ordprod.orderid,Ordsize.orderprodid, ; Sum(Ordsize.ordered) as TotOrdered, ; SUM(Ordsize.allocated) as TotAllocated, ; SUM(Ordsize.Shipped) as TotShipped, ;
OrdHead.SBO as SpecialOrd ;
From Ordsize,Ordprod,OrdHead ;
Where Ordprod.orderprodid=Ordsize.orderprodid ;
And OrdProd.OrderId =OrdHead.OrderID ;
Group by Ordsize.orderprodid

OrdHead,OrdPRod,OrdSize are all tables

as stated, the more I dig, he more complex it gets...

Thanks,
Mike