You would not use a file name inside a SQL query, You need the table name. That's the syntax error producing the ?
How many records are in the Users table? Is Email_User a text field with indexing turned on?
The table name in FileMaker > Manage Database is "USERS_XYZ" The name of the TO is "USERS".
There are 69,130 records in the USERS table.
Email_User is a text field and it is indexed.
If I replace USERS_XYZ with USERS in the Execute SQL command, I get a dialogue box (Records Remaining to Process) that freezes. It "sticks" at 69,130 and I have to force quit.
Thanks for your help.
I suggest recovering your file. It may be damaged. Test the recovered copy even if recover does not report finding/fixing any problems as the recovered copy will also have rebuilt indexes.
Things to keep in mind about Recover:
While Recover almost always detects and fully corrects any problems with your file...
- The recovered copy may behave differently even if recover reports "no problems found".
- Recover does not detect all problems
- Recover doesn't always fix all problems correctly
- Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.
And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).
Are you saying that the Execute SQL calc is correct and therefore the underlying file must be corrupt?
I did follow the recovery process and it found no problems.
I will follow the clone and import process.
FOLLOW UP: For what it's worth, I tried the same syntax on several other files and it didn't work on any so I'm doubting that file corruption is the issue.
UPDATE: More testing of ExecuteSQL Count(Distinct) points to problems related to the size of the file and number of unique values.
ExecuteSQL ("SELECT COUNT (DISTINCT State) FROM TO1_USERS" ; "" ; "") returns the correct answer almost immdiately.
Replacing "State" with "City" requires over 2 minutes to complete. During this time, th beachball is spinning and the operting system shows that FileMaker Pro Advanced is "not responding."
Replacing "City" with "Email_U" also appears to lock the system but it may just be too many email values to check. The file has over 69,000 records. It's possible that the calculation would eventually complete but it would take too long to be practical.
It's good to know that my file isn't corrupted though.
There may be ways to speed up the ExecuteSQL calculation but, in the meantime, I created a self-join on the field I want to count unique values. I added a calculation c_counter = 1/selfjoin::email_U and a summary field that totals c_counter. This seems to work fairly quickly.
Would it help to increase the File Cache Setting from the current 128MB to 196MB or above? What is the maximum file cache setting?
Anytime you force quit FileMaker, you should check it for damage. The "hang" that led to the force quit might be due to a damaged file and the force quit might damage your file.
And just because your file appears to function normally afterwards, does not mean that your file is undamaged.
I followed the Recovery instructions you provided in an earlier email (thank you). No errors were found.
But, you also said that finding no errors wasn't necessarily proof the file is OK.
How can one "check" the file for integrity if the Recovery tool doesn't provide a reliable result and if the file seems to work OK?
Gonna make me paranoid! :)
Sorry, but you never know that any file of any type on your system is not damaged. If it passes recovery with no problems found, it is very likely that your file is undamaged.
But recover also resets a few things to "factory specs" and rebuilds all indexes without checking them for problems so it's a good idea to test the recovered copy to see if it affects your results. Indexes, BTW, are used in searches and sorts so a damaged index could affect the results you get from this function.
Sorry - I misunderstood your instructions.
I know there's no guarantee that a file isn't corrupted. I thought you were suggesting specific ways to test the integrity but it sounds like you're suggesting some manual checks of sorting and searches to see if I can spot any errors.
Actually, I just had in mind that you try your SQL query again in the recovered file to see if it made any difference.
I did try the ExecuteSQL query again and I tested it with other fields in the same table as well as other tables. I described the results in this thread - perhaps 3 - 4 posts ago.
Bottom line, I'm under the impression that there were too many values in the Email_User for the ExecuteSQL Select Distinct query. The exact query with different fields worked - some slowly but the query didn't "like it" when I switched to the Email_User field.
I know, I'm just explaining why you would do that on the recovered copy of the file even if recover reports no problems found.
UPDATE: I think I just learned that ExecuteSQL is not always faster than built in FileMaker functions. The ExecuteSQL query listed in earlier posts in this thread actually does work. It's just incredibly slow.
Counting Unique email values in my table of 69,000 + records looks like it is going to take a little over 8 days ! to complete the process. It's taking about 11 seconds per record.
For those of you that know SQL - does this sound even remotely possible?
Also - is there a way to interrupt this process other than Force Quitting FileMaker?