1 2 Previous Next 15 Replies Latest reply on Mar 18, 2015 11:02 PM by philmodjunk

    Help with Execute SQL to count unique values

    sccardais

      Title

      Help with Execute SQL to count unique values

      Post

      I want to calculate / count the number of unique email addresses in my file.

      I defined a calculation field, c_UniqueEmails (number result) with the calculation shown below: 

      ExecuteSQL ("SELECT COUNT (DISTINCT Email_User) FROM USERS_XYZ" ; "" ; "")

      I've tried this with various Storage options. They all return "?" as a result.

      Email_User is a text field / indexed.

      The name of the FileMaker file is USERS_XYZ. The name of the TO is USERS. If I use USERS instead of USERS_XYZ, the progress bar appears but nothing happens. I need to force quit. If I use USERS_XYZ, the calculation returns a "?"

      The table has almost 70,000 records.

      What might I be doing wrong?

      FMPA 13

        • 1. Re: Help with Execute SQL to count unique values
          philmodjunk

          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?

          • 2. Re: Help with Execute SQL to count unique values
            sccardais

            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.

            • 3. Re: Help with Execute SQL to count unique values
              philmodjunk

              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...

                     
              1. The recovered copy may behave differently even if recover reports "no problems found".
              2.      
              3. Recover does not detect all problems
              4.      
              5. Recover doesn't always fix all problems correctly
              6.      
              7. 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).

              • 4. Re: Help with Execute SQL to count unique values
                sccardais

                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.

                Thanks.

                • 5. Re: Help with Execute SQL to count unique values
                  sccardais

                  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.

                  • 6. Re: Help with Execute SQL to count unique values
                    sccardais

                    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?

                     

                    • 7. Re: Help with Execute SQL to count unique values
                      philmodjunk

                      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. 

                      • 8. Re: Help with Execute SQL to count unique values
                        sccardais

                        Thanks.

                        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! :)

                        • 9. Re: Help with Execute SQL to count unique values
                          philmodjunk

                          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.  

                          • 10. Re: Help with Execute SQL to count unique values
                            sccardais

                            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.

                            Thanks

                            • 11. Re: Help with Execute SQL to count unique values
                              philmodjunk

                              Actually, I just had in mind that you try your SQL query again in the recovered file to see if it made any difference.

                              • 12. Re: Help with Execute SQL to count unique values
                                sccardais

                                Phil:

                                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.

                                • 13. Re: Help with Execute SQL to count unique values
                                  philmodjunk

                                  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.

                                  • 14. Re: Help with Execute SQL to count unique values
                                    sccardais

                                    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?

                                    1 2 Previous Next