>Just upgraded to v9 from v7. Working on an app originally created under v7. Have the following view:
>

>SELECT Ksongs.iid, Ksongs.cartist, Ksongs.ctitle, Ksongs.dlastprinted, ;
>     TRIM(Ksongs.cartist)+TRIM(Ksongs.ctitle) AS ufilter ;
>   FROM ksb!ksongs ;
>   WHERE Ksongs.ccatalog = ?vp_cCatalog ;
>          AND EMPTY(Ksongs.dlastprinted) = .T. ;
>          OR (Ksongs.dlastprinted > ?vp_SelectDate) ;
>   GROUP BY 5 ;
>   ORDER BY Ksongs.cartist, Ksongs.ctitle
>

>This worked great in v7 but fails as invalid group by clause in v9. Purpose of grouping was to insure that multiple versions of same artist/song combination printed as 1 item. I can't make sense of the syntax explanation in the v9 help file. Can anyone suggest how I might accomplish my goal using the v9 engine bahavior? Thanks

Hi Virgil,

In VFP9 the SQL is now in complaince with ANSI-SQL standard. This means, that any field not included into the GROUP BY, must be in some sort of an aggregate function.

For your query it means:

SELECT min(Ksongs.iid) as iID, min(Ksongs.cartist), min(Ksongs.ctitle), min(Ksongs.dlastprinted) as dLastPrinted, ;
     TRIM(Ksongs.cartist)+TRIM(Ksongs.ctitle) AS ufilter ;
   FROM ksb!ksongs ;
   WHERE Ksongs.ccatalog = ?vp_cCatalog ;
          AND EMPTY(Ksongs.dlastprinted) = .T. ;
          OR (Ksongs.dlastprinted > ?vp_SelectDate) ;
   GROUP BY 5 ;
   ORDER BY Ksongs.cartist, Ksongs.ctitle

BTW, in other languages you can not use newly created column in GROUP BY clause.