SQL statement doesnt works in VFP8 but works in VGP7

[2969]

hi

a simple statement such as this doesnt works in VFP8 but works in VFP7

'Select a.a, a.b, a.c, b.a from from a, b where a.x= b.x group by a.a"

the error it gives is "invalid use of Group "

any ideas??

thx

 

[wawalter]

I don't think that is a valid statement in "real" SQL which VFP 8 enforces. I believe there is a set statement that will force VFP 8 to use VFP 7 rules for SQL. I don't have 8 so I don't know it off hand, but I've seen it discussed here before. Do a search on VFP 8 and I'm sure you will find it.

 

[ramani]

HI

Use this command along with your SET commands..
SET ENGINEBEHAVIOR 70

This will solve your problem. But sooner or later, you have to modify the SQL codes to fit with VFP8 so that future is with compatible SQL codes to VFP 8

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[2969]

where do i put this se command...is it my VB application when i open the recordset???

 

[AirCon]

2969

I'm not really sure about this, cos you were asking SQL but mentioned about VB.

Just try this:
Select a.a, a.b, a.c, b.a from from a, b where a.x= b.x group by a.a, a.b, a.c, b.a

Hope it works

-- AirCon --

 

[niran]

I don't have any idea about this also but for ur SQL command seem alittle bit strange...
to me I know about this stucture :
Select ------- From ------- Where ----- Group by ------

But I never see the stucture like :

Select --- From --- From --- Where --- Group by ---

 

[craigsboyd]

Ramani and niran both have good points...I would add that your group doesn't really do anything as you have outlined it above and it may be throwing a fit because you aren't using any aggregate functions in your select clause..such as SUM() if this is the case then what Ramani has shown should fix the problem, then again if you really have FROM FROM in your SQL then that's a problem but I doubt it would throw an error about the group by clause, more likely to cause a syntax error.

Slighthaze = NULL

 

[Mike Lewis]

2969,

I just ran yor code. It works fine if you execute SET ENGINEBEHAVIOR 70. You need to do that somewhere in your code before you issue the SELECT. The start of the main program would be as good a place as any.

Slighthaze is right that this doesn't produce any useful results. Essentially, the result set will contain a row for each group, but the other fields will contain data from an arbitrary record within the group.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[AirCon]

Mike & Slighthaze,

Maybe I don't understand what 2969 wants to do. It does working if we use SET ENGINE.. from VFP. But I noticed his last posted. he said where he should put the command in his VB application, also he mentioned he is using recordset.

All of these thing confusing me. I'm sorry if I made a mistake.

About "FROM..FROM" things I believe it was a typo...

-- AirCon --

 

[Mike Lewis]

Aircon,

he said where he should put the command in his VB application, also he mentioned he is using recordset.


Yes, I was confused about that as well. It wasn't your mistake.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[2969]

hi Guys

i got the statement working, i used the SET ENGINEBEHAVIOR 70 when i open the connection.

conn.execute "SET ENGINEBEHAVIOR 70". Now the problem is that I have to use it at all the places where i open the code..

thx for the help.