Hi Michael,

Try change all sum(XXX)/sum(YYY)*100 to sum(XXX/YYY)*100

MartinJ

>I have a client who, upon migrating to VFP8 is getting a "SQL Expression is too complex" when running a query that he says ran fine in VFP6.
>
>SET ENGINEBEHAVIOR 70 does not fix it.
>
>The query has scads of SUM fieldname AS qfieldname (including some sum(...)/sum(...)*100 AS ...) and of course a simple GROUP BY.
>
>Did VFP8 lose some SQL marbles? Do I need to break this into two queries and join them?
>
>TIA
>
>

>	sele ;
>	sum(AN0) as AN0,;
>	sum(AA0) as AA0,;
>	sum(PA0) as PA0,;
>	sum(CA0) as CA0,;
>	sum(PA0)/sum(AA0)*100 as PP0,;
>	sum(AN1) as AN1,;
>	sum(AA1) as AA1,;
>	sum(PA1) as PA1,;
>	sum(CA1) as CA1,;
>	sum(PA1)/sum(AA1)*100 as PP1,;
>	sum(CA1)/sum(AA1)*100 as CP1,;
>	sum(CA1)/sum(AN1) as AR1,;
>	sum(AN2) as AN2,;
>	sum(AA2) as AA2,;
>	sum(PA2) as PA2,;
>	sum(CA2) as CA2,;
>	sum(PA2)/sum(AA2)*100 as PP2,;
>	sum(CA2)/sum(AA2)*100 as CP2,;
>	sum(CA2)/sum(AN2) as AR2,;
>	sum(AN3) as AN3,;
>	sum(AA3) as AA3,;
>	sum(PA3) as PA3,;
>	sum(CA3) as CA3,;
>	sum(PA3)/sum(AA3)*100 as PP3,;
>	sum(CA3)/sum(AA3)*100 as CP3,;
>	sum(CA3)/sum(AN3)*100 as AR3,;
>	sum(AN4) as AN4,;
>	sum(AA4) as AA4,;
>	sum(PA4) as PA4,;
>	sum(CA4) as CA4,;
>	sum(PA4)/sum(AA4)*100 as PP4,;
>	sum(CA4)/sum(AA4)*100 as CP4,;
>	sum(CA4)/sum(AN4)*100 as AR4,;
>	sum(AN5) as AN5,;
>	sum(AA5) as AA5,;
>	sum(PA5) as PA5,;
>	sum(CA5) as CA5,;
>	sum(PA5)/sum(AA5)*100 as PP5,;
>	sum(CA5)/sum(AA5)*100 as CP5,;
>	sum(CA5)/sum(AN5)*100 as AR5,;
>	sum(AN6) as AN6,;
>	sum(AA6) as AA6,;
>	sum(PA6) as PA6,;
>	sum(CA6) as CA6,;
>	sum(PA6)/sum(AA6)*100 as PP6,;
>	sum(CA6)/sum(AA6)*100 as CP6,;
>	sum(CA6)/sum(AN6)*100 as AR6,;
>	sum(AN7) as AN7,;
>	sum(AA7) as AA7,;
>	sum(PA7) as PA7,;
>	sum(CA7) as CA7,;
>	sum(PA7)/sum(AA7)*100 as PP7,;
>	sum(CA7)/sum(AA7)*100 as CP7,;
>	sum(CA7)/sum(AN7)*100 as AR7,;
>	sum(AN8) as AN8,;
>	sum(AA8) as AA8,;
>	sum(PA8) as PA8,;
>	sum(CA8) as CA8,;
>	sum(PA8)/sum(AA8)*100 as PP8,;
>	sum(CA8)/sum(AA8)*100 as CP8,;
>	sum(CA8)/sum(AN8)*100 as AR8,;
>	sum(AN9) as AN9,;
>	sum(AA9) as AA9,;
>	sum(PA9) as PA9,;
>	sum(CA9) as CA9,;
>	sum(PA9)/sum(AA9)*100 as PP9,;
>	sum(CA9)/sum(AA9)*100 as CP9,;
>	sum(CA9)/sum(AN9)*100 as AR9,;
>	sum(AN10) as AN10,;
>	sum(AA10) as AA10,;
>	sum(PA10) as PA10,;
>	sum(CA10) as CA10,;
>	sum(PA10)/sum(AA10)*100 as PP10,;
>	sum(CA10)/sum(AA10)*100 as CP10,;
>	sum(CA10)/sum(AN10)*100 as AR10,;
>	sum(AN11) as AN11,;
>	sum(AA11) as AA11,;
>	sum(PA11) as PA11,;
>	sum(CA11) as CA11,;
>	sum(PA11)/sum(AA11)*100 as PP11,;
>	sum(CA11)/sum(AA11)*100 as CP11,;
>	sum(CA11)/sum(AN11)*100 as AR11,;
>	sum(AN12) as AN12,;
>	sum(AA12) as AA12,;
>	sum(PA12) as PA12,;
>	sum(CA12) as CA12,;
>	sum(PA12)/sum(AA12)*100 as PP12,;
>	sum(CA12)/sum(AA12)*100 as CP12,;
>	sum(CA12)/sum(AN12)*100 as AR12,;
>	sum(AN13) as AN13,;
>	sum(AA13) as AA13,;
>	sum(PA13) as PA13,;
>	sum(CA13) as CA13,;
>	sum(PA13)/sum(AA13)*100 as PP13,;
>	sum(CA13)/sum(AA13)*100 as CP13,;
>	sum(CA13)/sum(AN13)*100 as AR13,;
>	sum(AN14) as AN14,;
>	sum(AA14) as AA14,;
>	sum(PA14) as PA14,;
>	sum(CA14) as CA14,;
>	sum(PA14)/sum(AA14)*100 as PP14,;
>	sum(CA14)/sum(AA14)*100 as CP14,;
>	sum(CA14)/sum(AN14)*100 as AR14,;
>	sum(AN15) as AN15,;
>	sum(AA15) as AA15,;
>	sum(PA15) as PA15,;
>	sum(CA15) as CA15,;
>	sum(PA15)/sum(AA15)*100 as PP15,;
>	sum(CA15)/sum(AA15)*100 as CP15,;
>	sum(CA15)/sum(AN15)*100 as AR15,;
>	div, divorder, client, cliorder, name, keyname, dsk, 'k' as rptgrp;
>	from btall;
>	group by keyname,div;
>	into table data\btkn
>