>The following sql select statement has been working fine in Foxpro version 2.6 for Windows and compiles w/o error. When we compile the program containing the sql select statement in Visual Foxpro ver. 5.0 (upgrading a client's system), we get a syntax error. Further investigation indicates that it is having a problem w/the "group by" clause. Does anyone know why there is now a syntax error in VFP 5.0? Any ideas how to fix it? Thanks in advance for the help.
>
>SELECT a.item, a.loctid FROM a_ictran a, a_icitem b ;
> WHERE RIGHT(a.trantyp,1) = "R" ;
> .AND. BETWEEN(a.item, lc_bitem, lc_eitem) ;
>UNION ;
> SELECT a.item, a.loctid FROM a_icytrn a, a_icitem b ;
> WHERE RIGHT(a.trantyp,1) = "R" ;
> .AND. BETWEEN(a.loctid, lc_bloca, lc_eloca) ;
> INTO CURSOR temp1 ;
> GROUP BY 1 ;
> ORDER BY 1


We found the same problem. The VFP SELECT-SQL help has the solution: put the GROUP BY option *before* the UNION keyword.

HTH!