SQL-problem VFP8 >> VFP9 1

[Nifrabar]

Hi!
I migrated my app from VFP8 > 9.
I know there has changed some in the syntax I have to use.
The strange thing is that underlisted code mostly works fine but sometimes ends up in error.
Anyone who can give me a (generic) hand on how to modify my queries? I thought it has to deal with the group-by clause?
TIA
-Bart

* recordsource for grid, created by code 
 this.parent.parent.recordsource='SELECT Xadres,;
  sum(kb) as Aantal,count(*)as bewoners,    sum(aantgewist) as gewisten,lid_id, kerkblad,;
  adres,huisnummer,huisnumtoevoeg,plaats;
  FROM temp;
  GROUP BY Xadres;
  HAVING  sum(kb) <>1;
  into cursor temp1;
  order by 1 ASC'
  gnKolom=1

 

[Nifrabar]

oops...
found same problem in thread directly hereunder...
-Bart

 

[JimWWinter]

Bart,

Right, your group by needs to be

GROUP BY Xadres, lid_id, kerkblad,;
adres,huisnummer,huisnumtoevoeg,plaats ;

Regards,
Jim

 

[Nifrabar]

Thanks Jim,
Another problem arise than.
As the SQL-string is in quotes the total length exceeds the max. allowed lenght. So I need to look for another solution.
Probably by creating a cursor in my init or load-method.
The code as displayed is in every click-method of a grid.
Depending on the column# the query is ordered on that particular field of the grid.
-Bart

 

[Mike Lewis]

Bart,

If you are creating a sortable grid, could I make a suggestion.

Rather than executing the SELECT in the Click of every column, you should run the query in the grid's Init, but without the ORDER BY clause. Also, add READWRITE after INTO CURSOR Temp1. Set the grid's RecordSource to the alias of this cursor.

Then, in the individual click events, create an index on the cursor, based on the column's ControlSource.

Also, maintain an array, with one element per column. The element will contain a logical field. As soon as you have created an index, set the corresponding element to .T. If the element is already .T., don't recreate the index, just SET ORDER to it.

This might sound like a lot of work, but it is usually a more efficient way of creating a sortable grid, especially with large tables. I have this in my base class library, and it works very well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Nifrabar]

Mike,

Thanks for your additional advice.
I did what you adviced. Although my tables are not so big in this app. I like the good behaviuor on coding always.

I tried to find the index-file on disk but couldnot find.
What is the usual location for it?

I like to check if the file is gone after closing the app.

-Bart

 

[Mike Lewis]

Bart,

You don't need to take any special action to delete the index file. It will be deleted automatically when you close the cursor.

However, if you want to be sure, use the DBF() function to locate the underlying physical file for the cursor. The index file will be in the same location.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Nifrabar]

Mike,
Thanks again.
I think you'r solution for sorting teh grid this way by creating the index this way is worth a star!
-Bart

 

[Mike Lewis]

Glad it was useful, Bart. Thanks for the star.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)