12 Replies Latest reply on Dec 5, 2012 9:52 AM by jbante

    Execute sql standard deviation

    Hudi

      For some reason this works fine:

       

      ExecuteSQL (" SELECT AVG("Grade") FROM"Test" WHERE ("Student") = ?"

      ; "" ; "";Student::Name)

       

      But this returns and error (?)

       

      ExecuteSQL (" SELECT STDEV("Grade") FROM"Test" WHERE ("Student") = ?"

      ; "" ; "";Student::Name)

       

       

      What gives?

        • 1. Re: Execute sql standard deviation
          LSNOVER

          I don't believe Filemaker supports that function.  It does not fully support ANSI Standard SQL.

          • 2. Re: Execute sql standard deviation
            Hudi

            Thanks for the response Lee,

             

            I got the idea that it could work from this: http://www.filemakerhacks.com/wp-content/uploads/2012/10/The-Missing-FM-12-ExecuteSQL-Reference.pdf

             

            Have a look at page 16. Seems like its supposed to work.

             

            Hmmm

            • 3. Re: Execute sql standard deviation
              beverly

              Lee, Do you have a source for what's supported and what's not? I know in my tests, I only went with trial-and-error to figure out what works. Some MS-SQL and some MySQL functions worked. A lot of them did not. Some didn't get tested, but are noted as such in the example database.

               

              STDEV is a built-in function for FMP, so I wonder why Hudi_M used ExecuteSQL. I would see no advantage.

              Beverly

               

              I don't believe Filemaker supports that function.  It does not fully support ANSI Standard SQL.

               

              • 4. Re: Execute sql standard deviation
                Hudi

                I was hoping it would make things faster for me. I'm running into some performance issues because I have about 7 fields calculating stdev. With the FM function it's an unstored calc because its referncing a related table . I also have calcs that are using the StDev calcs so I was hoping that by making the StDev calcs local and indexed it would speed things up.

                RIght now, the layout that is based on this table is super slow.

                • 5. Re: Execute sql standard deviation
                  LSNOVER

                  Hi Bev:

                   

                  I'm just going off of the fm12_odbc_jdbc guide and trial and error.

                   

                  Hudi, that's Bev's white paper, not a official FM document. 

                   

                  Beverly, did you get that function to work at some point?  I've never seen it work in FM.  I could certainly use it in FMSQL.

                   

                  Regards,

                  Lee

                  • 6. Re: Execute sql standard deviation
                    Hudi

                    Yea I realize, but I was just hoping she had a trick or two up her sleeve.

                    Oh well.

                     

                    Thanks anyway guys

                    • 7. Re: Execute sql standard deviation
                      beverly

                      No, Lee the xDBC guide has to do with FM as source mostly. It bears little usefulness for ExecuteSQL.

                      Thus the "missing reference" and my trial and error. I used what I could find from others tests, too, although I verified everything I tested in my example file. I did not test all the functions (just most of them). The example file shows what was tested more than the white paper does.

                       

                      STDEV was one of the SQL functions I did not test.

                       

                      At some point it would be nice to get "the under-the-hood" on this function. There seems to be little rhyme or reason as to what works and what doesn't. Other than those SQL functions that change the data in the fields - I don't expect them to work, I wonder what "list" was used for ExecuteSQL(). As I said some of the Transact-SQL functions worked and some of the MySQL functions worked. We both know that there's very little that is "standard" about SQL - LOL.

                       

                      Beverly

                      • 8. Re: Execute sql standard deviation
                        beverly

                        LOL! I only report on what I discover. Sorry.

                        Beverly

                        • 9. Re: Execute sql standard deviation
                          gdurniak

                          This is what surprised me as well

                           

                          I always thought it was the same FM SQL,  just accessed on itself

                           

                          From your guide it appears as if ExecuteSQL is it's own proprietary "engine"

                           

                          greg

                           

                           

                          > No, Lee the xDBC guide has to do with FM as source mostly. It bears little usefulness for ExecuteSQL.

                          • 10. Re: Execute sql standard deviation
                            LSNOVER

                            Yeah, the standard is not very standard.  lol.   But FMI needs to get the feature set up to par with ODBC and the ExecuteSQL command.  They've come a LONG way the last few releases, but it needs some polishing.

                            • 11. Re: Execute sql standard deviation
                              LSNOVER

                              Hudi:

                               

                              You could try doing your own STD DEViation calc with SQL, but you will have to do it in a few passes.  You have to get the average first, then you can do the remainder in one SQL call I believe using the sqrt function which DOES work.  Not simple, but it MIGHT be a tad faster.  If I can spare a few minutes I will give it a go myself.

                               

                              Regards,

                              Lee

                              1 of 1 people found this helpful
                              • 12. Re: Execute sql standard deviation
                                jbante

                                I'm not good enough with SQL to know how I might roll my own standard deviation calculation or why it might need to be a multi-pass calculation, but from the math side there's no reason why you should need a multi-pass algorithm to calculate the standard deviation. You just have to maintain multiple variables on the single pass, like this function I made for updating running performance statistics without re-referencing all the past data.

                                1 of 1 people found this helpful