12 Replies Latest reply on Aug 8, 2017 2:58 PM by user19752

    Using ExecuteSQL to find Avg Elapsed Time

    xxx202xxx

      Having trouble with my query. All I get is a freaking ?  Help!

       

      ExecuteSQL ("

      SELECT AVG (c_Resolved_Elapsed)
      FROM Service_Management
      WHERE Priority=? AND MONTH(Date_Created)=?"

      ;" - "; ""; "Critical"; Month ( Get ( CurrentDate ) ))

        • 1. Re: Using ExecuteSQL to find Avg Elapsed Time
          coherentkris

          i would suggest that the ? is being generated by MONTH(Date_Created)=? because FileMaker dates are not equal to SQL dates.. The cast operator might help but i make no guarantees.

          Perhaps...MONTH (||date_created)...

          https://www.seedcode.com/executesql-date-formats/

           

          From https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

          "Date & Time functions that work well in ExecuteSQL() and may be used with the System functions, above or your date fields or even the properly formatted Date/Time text (in single quotes ‘YYYY-MM-DD hh:mm:ss’):"

           

          From the SQL reference:

          https://fmhelp.filemaker.com/docs/14/en/fm14_sql_reference.pdf

          PG24...

          "The FileMaker Pro ExecuteSQL function accepts only the SQL-92 syntax ISO date and time formats with no braces."

          farther down it says...

          "MONTH(DATE '2015-01-30')"

          So i am guessing that if MONTH() is fed anything but a SQL-92 std DATE it will throw ?

          • 2. Re: Using ExecuteSQL to find Avg Elapsed Time
            xxx202xxx

            Ok, let scrap that Idea.  Going the relationship route How would I get this done?

            • 3. Re: Using ExecuteSQL to find Avg Elapsed Time
              beverly

              Nice reply, co!

              I cannot test, but if you put EvaluationError() around your ExecuteSQL(), you may get the proper error code.

              OTOH, the aggregate in the WHERE clause, likely is the problem (or part of it). Try HAVING instead. And you may also need the GROUP BY (placed above the HAVING) clause.

               

              Sent from miPhone

              • 4. Re: Using ExecuteSQL to find Avg Elapsed Time
                greglane

                What type of result does the c_Resolved_Elapsed field return? FileMaker won't apply the SQL AVG function to a time field. FileMaker Pro 16 Advanced's Data Viewer would show the following error if that's the case:

                 

                     Parameter number 1 to the function "AVG" is not of the correct type.

                 

                You could change the result type to a number and your query would likely work.

                1 of 1 people found this helpful
                • 5. Re: Using ExecuteSQL to find Avg Elapsed Time
                  xxx202xxx

                  c_Resolved_Elapsed is a timestamp field.  The funny thing is I have the average listed as a Leading Grand Summary on a table view I just want to put this average value in a field.  But yeah its a timestamp

                  • 6. Re: Using ExecuteSQL to find Avg Elapsed Time
                    xxx202xxx

                    I was able to get it to work by doing something different.  I had created two fields:

                     

                    1. c_ResolvedCritical -> ServiceMGMT::Priority = "Critical" and c_Month (another field created a while ago)

                    2.c_ResolvedCriticalAvg ->  Avg (c_ResolvedCritical)

                    • 7. Re: Using ExecuteSQL to find Avg Elapsed Time
                      fmpdude

                      If you're doing SQL .... You should be using a real SQL tool!

                       

                      Check out RazorSQL as one example as it will do so much more than just get rid of the ridiculous and unhelpful ... ?

                       

                      Often the error isn't at all what you thought.

                       

                      Had you been using a real SQL Tool (or FMP 16), you would have seen the error right away and not been pulling your hair out and wasting your time. I don't even consider using FMP and SQL without a separate tool. I am hoping that FMP 17 or FMP 18 will be better in basic SQL capabilities beyond just showing an error message.

                       

                      ERROR REPORTED FROM SQL TOOL: [08007] [FileMaker][FileMaker JDBC] FQL0021/(1:7): Parameter number 1 to the function "AVG" is not of the correct type.

                       

                       

                      You can't average a timestamp field: It doesn't make sense.

                       

                      ----------------

                       

                      If I add a number field, called "number", then AVG works fine:

                      So, ....

                       

                      -----

                       

                      The answer to how you do what you want to do is to calculate, say, the amount of time in minutes, seconds, or whatever something took. So, for example, a possible good approach would be to have a starting TIMESTAMP and an ending TIMESTAMP. Then, you take the difference of these possibly in a calculated field. Then take the AVG of the calculated field using those numeric timestamp difference values.

                       

                      Be careful, though, since (amazingly) FMP doesn't seem to understand its own TIMESTAMP data type with other FMP functions. You may need an intermediate CF for that conversion just to do the date math.

                       

                      HOPE THIS HELPS.

                      3 of 3 people found this helpful
                      • 8. Re: Using ExecuteSQL to find Avg Elapsed Time
                        user19752

                        FM does auto type conversion when need, SQL doesn't.

                         

                        Saying that, but FMSQL return local date format when concatenated with empty string.

                        SELECT ''||dateOrTimestamp

                        in this calculation dateOrTimestamp is converted to formatted text...

                        • 9. Re: Using ExecuteSQL to find Avg Elapsed Time
                          fmpdude

                          I think you're missing the basic point: it doesn't make sense to average a Timestamp.

                           

                          That's the logical error here; it's not SQL vs. FMP thing.

                          • 10. Re: Using ExecuteSQL to find Avg Elapsed Time
                            beverly

                            "timestamp" is really a number (of elapsed time), but if the user has chosen to return time/timestamp result in the calucaltion, that's perfectly legal and something I might want to display rather than the number of seconds. (for example):

                            elapsed_time_c = endTime - startTime

                            GIVEN:

                            •      startTime = 8:00
                            •      endTime = 10:00

                            OUTPUT:

                            •      result (time) = 2:00:00
                            •      result (number) = 7200

                            Let (

                            [ startTime = GetAsTime("8:00")

                            ; endTime = GetAsTime("10:00")

                            ; elapsed = endTime - startTime

                            ; TS = GetAsTime(elapsed)

                            ; NUM = GetAsNumber(elapsed)

                            ; result = TS & Char(13) & NUM

                            ]; result

                            )

                            Beverly

                            • 11. Re: Using ExecuteSQL to find Avg Elapsed Time
                              fmpdude

                              Right....I think we're saying the same thing: don't average a single timestamp field by itself, but rather a elapsed (difference) of two timestamps.

                              • 12. Re: Using ExecuteSQL to find Avg Elapsed Time
                                user19752

                                I agree.

                                timestamp can't be added to timestamp, then can't calculate their average.

                                PostgreSQL: Documentation: 9.6: Date/Time Functions and Operators

                                FM does auto convert to difference ("interval" in pgsql) or rather say it is already difference in internal value.

                                1 of 1 people found this helpful