Mike Yearwood
Toronto, Ontario, Canada
General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>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...)
HMMMM
You'd have to add the following fields to the group by:
Invhead.orderid, Invhead.invoiceno, Invhead.invoicedate,;
Customer.Code as Code, Invhead.freight, Invhead.brokerage, ;
Ordhead.entered, Customer.name AS custname, Ordhead.location, ;
Invprod.invoiceqty
Invprod.invoiceqty seems out of place. Maybe you need to sum on that?
>
>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only