Discussion:
VFP 6 & 8
(too old to reply)
Wilson
2005-01-30 03:16:38 UTC
Permalink
Friends,

I was using the following query ini VFP 6:

SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves

After I migrated to VFP8 this query gives error as "SQL: GROUP BY clause is
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.

This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!

Any idea to solve this issue?

Thanks in advance

Wilson
Wilson
2005-01-30 03:34:07 UTC
Permalink
Friends,

I changed SET ENGINEBEHAVIOR 70 and it worked.

thanks

Wilson
Post by Wilson
Friends,
SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves
After I migrated to VFP8 this query gives error as "SQL: GROUP BY clause is
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.
This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!
Any idea to solve this issue?
Thanks in advance
Wilson
Fred Taylor
2005-01-30 08:45:34 UTC
Permalink
The real answer is "no, it doesn't". It's giving you ambiguous results, and
this was corrected in VFP8. While it may have "seemed" to be working for
you before, the contents of lev_name can not be determined which record it
actually comes from. If you add lev_name to the GROUP BY clause or an
aggregate of lev_name (MIN(lev_name or MAX(lev_name)) as the field, it will
be syntactically correct SQL, the same as SQL server would require.
--
Fred
Microsoft Visual FoxPro MVP
Post by Wilson
Friends,
I changed SET ENGINEBEHAVIOR 70 and it worked.
thanks
Wilson
Post by Wilson
Friends,
SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves
After I migrated to VFP8 this query gives error as "SQL: GROUP BY clause
is
Post by Wilson
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.
This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!
Any idea to solve this issue?
Thanks in advance
Wilson
Wilson
2005-01-30 22:58:43 UTC
Permalink
If all lev_name is same for each group, will it be ok?
Will it mix lev_name & SUM total from different group

Thanks

Wilson
Post by Fred Taylor
The real answer is "no, it doesn't". It's giving you ambiguous results, and
this was corrected in VFP8. While it may have "seemed" to be working for
you before, the contents of lev_name can not be determined which record it
actually comes from. If you add lev_name to the GROUP BY clause or an
aggregate of lev_name (MIN(lev_name or MAX(lev_name)) as the field, it will
be syntactically correct SQL, the same as SQL server would require.
--
Fred
Microsoft Visual FoxPro MVP
Post by Wilson
Friends,
I changed SET ENGINEBEHAVIOR 70 and it worked.
thanks
Wilson
Post by Wilson
Friends,
SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves
After I migrated to VFP8 this query gives error as "SQL: GROUP BY clause
is
Post by Wilson
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.
This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!
Any idea to solve this issue?
Thanks in advance
Wilson
Fred Taylor
2005-01-31 03:53:24 UTC
Permalink
If they're all the same, then you cando it either way, put lev_name in the
GROUP BY or do a MIN() or a MAX() in the field list.
--
Fred
Microsoft Visual FoxPro MVP
Post by Wilson
If all lev_name is same for each group, will it be ok?
Will it mix lev_name & SUM total from different group
Thanks
Wilson
Post by Fred Taylor
The real answer is "no, it doesn't". It's giving you ambiguous results,
and
Post by Fred Taylor
this was corrected in VFP8. While it may have "seemed" to be working for
you before, the contents of lev_name can not be determined which record it
actually comes from. If you add lev_name to the GROUP BY clause or an
aggregate of lev_name (MIN(lev_name or MAX(lev_name)) as the field, it
will
Post by Fred Taylor
be syntactically correct SQL, the same as SQL server would require.
--
Fred
Microsoft Visual FoxPro MVP
Post by Wilson
Friends,
I changed SET ENGINEBEHAVIOR 70 and it worked.
thanks
Wilson
Post by Wilson
Friends,
SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves
After I migrated to VFP8 this query gives error as "SQL: GROUP BY
clause
Post by Fred Taylor
Post by Wilson
is
Post by Wilson
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.
This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!
Any idea to solve this issue?
Thanks in advance
Wilson
Anders Altberg
2005-01-31 13:38:43 UTC
Permalink
SELECT leave_code,lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code. lev_name ;
INTO CURSOR vPaySlipLeaves

-Anders
Post by Wilson
If all lev_name is same for each group, will it be ok?
Will it mix lev_name & SUM total from different group
Thanks
Wilson
Post by Fred Taylor
The real answer is "no, it doesn't". It's giving you ambiguous results,
and
Post by Fred Taylor
this was corrected in VFP8. While it may have "seemed" to be working for
you before, the contents of lev_name can not be determined which record it
actually comes from. If you add lev_name to the GROUP BY clause or an
aggregate of lev_name (MIN(lev_name or MAX(lev_name)) as the field, it
will
Post by Fred Taylor
be syntactically correct SQL, the same as SQL server would require.
--
Fred
Microsoft Visual FoxPro MVP
Post by Wilson
Friends,
I changed SET ENGINEBEHAVIOR 70 and it worked.
thanks
Wilson
Post by Wilson
Friends,
SELECT lev_name, SUM(leave_acc) ;
FROM vEmpPayLeaves ;
GROUP BY leave_code ;
INTO CURSOR vPaySlipLeaves
After I migrated to VFP8 this query gives error as "SQL: GROUP BY
clause
Post by Fred Taylor
Post by Wilson
is
Post by Wilson
missing or invalid". But if I exclude lev_name (fields without SUM) it
works.
This query still works in VFP6
My database is Access. I tried to run this query in Access and it works
fine!
Any idea to solve this issue?
Thanks in advance
Wilson
Loading...