what is wrong on this prg file that does not run on VFP 9.0 2

[Ernesto_Paez]

Hi,
I am able to run this prg file in VFP 5.0, however in VFP 9.0 SP2 if fails on the "group by" also i need to use INNER JOIN here is the code below,can anyone help on what could be wrong, the machine where i have vfp 5.0 installed is down, so i tried in VFP 9.0 and the error i get is "group by clause is missing or invalid"
Thanks in advance.

set safety off
sele 1
use arcust04 shared
sele 2
use arYtrn04 shared
SET CENTURY on
store ("01/01/2018") to date1
store ("12/31/2018") to date2
SELECT Arcust04.custno, Arcust04.company, Arcust04.city, sum(arYtrn04.bextpri);
 FROM  arcust04 INNER JOIN arYtrn04 ;
   ON  Arcust04.custno = arYtrn04.custno;
 WHERE  arYtrn04.current<>"X" AND arYtrn04.invdte >= (date1);
   AND arYtrn04.invdte <= (date2);
 GROUP BY Arcust04.custno;
 ORDER BY 3;
 INTO TABLE newtemp.dbf
 go top
index on sum_bextpr tag  extp descending && added to desplay data from higher to lower
browse
store " Apex" to comp_name
store "History" to status
report form detinv  preview  &&to printer prompt
use newtemp excl
index ON CUSTNO TO cus  && this was part of the above line
GO TOP
brow
copy to g:\users\chrisba\apex123118 type xls
zap
close all

 

[Tore Bleken]

Check Set EngineBehavior in Help, and the linked topics. And do NOT be tempted to set this to a low value!! The change was for a very good reason, which is described in Help. Help will also tell you how to fix the select statement. In short, every field you select which are not calculated, must also be included in the Group by list of fields.

In this specific case, this will hopefully work:

[pre]SELECT Arcust04.custno, Arcust04.company, Arcust04.city, sum(arYtrn04.bextpri);
FROM arcust04 INNER JOIN arYtrn04 ;
ON Arcust04.custno = arYtrn04.custno;
WHERE arYtrn04.current<>"X" AND arYtrn04.invdte >= (date1);
AND arYtrn04.invdte <= (date2);
GROUP BY Arcust04.custno,Arcust04.company, Arcust04.city;
ORDER BY 3;
INTO TABLE newtemp.dbf[/pre]

 

[Olaf Doschke]

You only group by CUSTNO, then any other field you have in the field list has to be an aggregation function, MIN, MAX, AVG, etc.

SQL got stricter with VFP8 about this group by behaviour. Think about it, within one group of records with the same CUSTNO there can be multiple values of other fields. Which should be put into the result?

Even when your case is working and company and city are unique per CUSTNO and all the same within one group, this is wrong by ANSI SQL standards but easy to fix by using MIN() on both of them (or, as Tore did, add them to the GROUP BY).

It depends on your data, what you need to do. Notice the more fields you group by, the more special groups get, and that means smaller group and more result records. If you want the first city of a few per group and neither the MIN() or MAX(), that needs a self join or a subquery. But welcome to this century, this has been that way in VFP since about 2003, for almost exactly 16 years (VFP8 was released on 1st February 2003).

You can ignore this and continue with the wrong SQL engine by setting SET ENGINEBEHAVIOUR 70, but I strongly recommend fixing the SQL to reflect the case as it needs to be handled by ANSI standards.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Another topic: You store strings to date1 and date2 variables, Putting them in brackets does not change that.
You may get a surprise about the range of data you select. To get the start and end dates of 2018 you could use DATE(2018,1,1) and DATE(2018,12,31) or date literals {^2018-01-01} and {^2018-12-31}.
Or you query invdte>=DATE(2018,1,1) AND invdte<DATE(2019,1,1) and exclude the 1st January 2019 that way but have a simple DATE(Year,1,1) for both dates of the interval.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Thanks a lot i did not know about it thanks guys.