Jump to content

FM 12 ExecuteSQL statement not working...seems simple...


This topic is 4277 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Can someone help me figure out why this, apparently, simple ExecuteSQL statement doesn't work?

post-105601-0-78849900-1339817768_thumb. The short description is that we are trying to count the occurrence of words. The table has some 4100+ records, each one (the "Text" field) simply a word from a document. I was trying to test if putting a numeric identifier for a word, instead of the text word itself, would be faster. (The text based 'Group By...' takes 25 seconds!!) So I have been trying to test whether different techniques will help (indexing, this numeric field thing, etc). Here's a sample of my data:

ExecuteSQL (

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY GroupNum"

;"";""

)




While this version does (only removed the "Text" field from the Select):


ExecuteSQL (

"SELECT GroupNum, Count (GroupNum)

FROM Test

GROUP BY GroupNum"

;"";""

)




I have a very simple table structure as I was just doing some SQL testing.  See attached image cap.




availability    96

availability    96

available    97

average    98

awake    99

aware    100

aware    100

away    101

back    102

back    102

bag    103

balance    104

ball    105

barely    106

barometer    107

barrel    108

barrel    108

barrels   109 

So, my SQL is just designed to give me the resulting count of words, in one field ("Results"). So I wanted to make it prettier by including the "Text" word as well as the numeric version.

Thanks,

J

Link to comment
Share on other sites

Hi there,

when using an aggregate function (such as COUNT, SUM...), you MUST put all the other columns (ie that are not aggregated) in the GROUP BY.

Therefore, try :

ExecuteSQL(

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY Text, GroupNum"

;"";""

)

  • Like 2
Link to comment
Share on other sites

Hi there,

when using an aggregate function (such as COUNT, SUM...), you MUST put all the other columns (ie that are not aggregated) in the GROUP BY.

Therefore, try :

ExecuteSQL(

"SELECT Text, GroupNum, Count (GroupNum)

FROM Test

GROUP BY Text, GroupNum"

;"";""

)

Laurent,

Thanks for the tip. It seems like an odd requirement, however. But, it did work...to an extent. Unfortunately, it is now doing TWO Group By operations, and so now takes 38 seconds instead of 19. However, that is still slightly faster than doing the SQL against the Text field directly (about 22 seconds for that one).

Thanks,

J

Link to comment
Share on other sites

How many records are we talking about? 22 seconds seems awfully long for that. I have a layout with about 100 separate calls that groups and sums, the largest data sets take at most 15 seconds.

Link to comment
Share on other sites

  • 3 weeks later...

This topic is 4277 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.