1 2 Previous Next 25 Replies Latest reply on Aug 29, 2016 10:12 AM by beverly

    ExecuteSQL Count

    cortical

      This returns a count:

       

      Let([

      vA = $_investigation_id ;

      vB = $_gpb_id ;

      vC = $_side_opposite ;

       

      ~query = "

      SELECT  COUNT (value_nr_id)

      FROM    ValueNR

      WHERE   investigation_id = ? AND group_b_id= ?  AND nr_side = ?

      "

      ];

      ExecuteSQL ( ~query ; "," ; "¶" ; vA ; vB ; vC   )

      )


       

      But when the calculation is expanded to use a standard SQL grammar I am using successfully in a quantity of other scripts , it fails to return a count. Without the COUNT in below all works as expected.

      I have tried several variations on including the COUNT function, in the RSLT variable for example, suitably quoted... and checked the ~query and ~sqlquery results,   but the below at least returns the same string as what does work in the above Let.


      Let([

       

      //input

      va = $_investigation_id ;

      vb = $_gpb_id ;

      vc = $_side_opposite ;

       

      //result

      RSLT = sqlFN ( ValueNR::value_nr_id ) ;

       

      // from table

      TBL = sqlTN ( ValueNR::value_nr_id ) ;

       

      //where field:

      fa = sqlFN( ValueNR::investigation_id) ;

      fb = sqlFN( ValueNR::group_b_id) ;

      fc = sqlFN( ValueNR::nr_side) ;

       

      ~query = "

      SELECT COUNT( ~result )

      FROM ~table

      WHERE ~fa =? AND ~fb = ? AND ~fc = ?

      "

      ;

      ~sqlQuery = Substitute( ~query;

      ["~result" ; RSLT ] ;

      ["~table" ; TBL ] ;

      ["~fa" ; fa ] ;

      ["~fb" ; fb ] ;

      ["~fc" ; fc ]

      )

      ];

      ExecuteSQL ( ~sqlQuery ; "," ; "¶" ; va ; vb ; vc )

      )


       

      examining the ~query and the ~sqlquery, thinking am I missing some invisible characters that are causing the fail, the text has some spacing differences, so applying a Trim4, leaves some horiziontal tab &#x9 characters , also substituted out to return what appears to be identical strings


      Or is it a simple syntax issue?  wrapping the ~result variable in the Count for example.


        trimming out all wjite space characters to examine the query string:

       

      ~sqlQuery = Trim4(Substitute( ~query;

      ["~result" ; RSLT ] ;

      ["~table" ; TBL ] ;

      ["~fa" ; fa ] ;

      ["~fb" ; fb ] ;

      ["~fc" ; fc ]

      ))

      ;

      A = Trim4(Substitute( ~sqlquery ; [" " ; ""] ; ["¶" ; ""] )) ;

      B = Substitute( A; " "; "")

      ];

      ExecuteSQL ( B ; "," ; "¶" ; va ; vb ; vc   )

      )

       

      returns strings that appear identical

       

      SELECTCOUNT(value_nr_id)FROMValueNRWHEREinvestigation_id=?ANDgroup_b_id=?ANDnr_side=?


       

      SELECTCOUNT(value_nr_id)FROMValueNRWHEREinvestigation_id=?ANDgroup_b_id=?ANDnr_side=?

        • 1. Re: ExecuteSQL Count
          siplus

          You can always use ValueCount() on the result of a SQL Query in which you just select, without counting.

          • 2. Re: ExecuteSQL Count
            cortical

            yes, I have done that previously. The object is not only to get the COUNT to work with a  variable, but to thereby establish a grammar that can be used with other SQL functions

            • 3. Re: ExecuteSQL Count
              steve.thoms

              You can make the query a bit strong by using COUNT(1) instead of any field. Using a constant like 1 will make the query faster as well. -Steve

              • 4. Re: ExecuteSQL Count
                cortical

                That works! as does  SELECT  COUNT(  * ) which I had just been trying.

                 

                so leaving out the result declaration altogether works.

                 

                 

                Let([

                 

                //input

                va = $_investigation_id ;

                vb = $_gpb_id ;

                vc = $_side_opposite ;

                 

                //result

                 

                // from table

                TBL = sqlTN ( ValueNR::value_nr_id ) ;

                 

                //where field:

                fa = sqlFN( ValueNR::investigation_id) ;

                fb = sqlFN( ValueNR::group_b_id) ;

                fc = sqlFN( ValueNR::nr_side) ;

                 

                ~query = "

                SELECT  COUNT(  * )

                FROM    ~table

                WHERE   ~fa  =? AND  ~fb = ? AND  ~fc = ?

                "

                ;

                ~sqlQuery = Substitute( ~query;

                ["~table" ; TBL ] ;

                ["~fa" ; fa ] ;

                ["~fb" ; fb ] ;

                ["~fc" ; fc ]

                )

                ];

                ExecuteSQL (~sqlQuery ; "," ; "¶" ; va ; vb ; vc   )

                )

                 

                 

                thanks Steve

                • 5. Re: ExecuteSQL Count
                  cortical

                  the questions remains of course, why does the same ~sqlquery string work in one construct and not the other.

                  • 6. Re: ExecuteSQL Count
                    siplus

                    nice to have academic interest. I have clients, instead.

                     

                    On a database with 7'000'000 records I did 20'000 ExecuteSQLs, 10k with count, 10k without (but with valuecount() of the result).

                     

                    total time for the Sql with count: 622245 ms.

                    total time for SQL + valuecount: 139692 ms.

                    • 7. Re: ExecuteSQL Count
                      cortical

                      I have been replacing just under 40 script steps in 14 scripts with ExecuteSQL over the last 2 weeks, and analysing for speed comparison of LAN vs client, and fm step (list...) vs SQL.

                       

                      The layouts are a bit loaded,  2 popovers,  up to 8 slide control panels on each SCP,  10-15 fields, checkboxes, radiobuttons, edit boxes, all as globals, plus related comments. Opening the  popover loads related normalised data into the globals, etc.

                      Plus there is typical 40 conditional format objects per SCP.  So it isa  pul/push scenario.

                       

                      LAN open popover an d load was c. 2700 msec. with FM steps; with SQL c. 500msec

                       

                      Database on the laptop:  FM c. 300 ms, SQL c. 40 ms

                      • 8. Re: ExecuteSQL Count
                        siplus

                        this is the script:

                         

                         

                        Set Variable [ $i ; Value: 0 ]

                        Set Variable [ $sqlcount ; Value: 0 ]

                        Set Variable [ $valuecountofsql ; Value: 0 ]

                        Set Variable [ $$data ; Value: "" ]

                        Allow User Abort [ Off ]

                        Freeze Window

                        Loop

                        Exit Loop If [ Let($i = $i + 1; $i > 10000) ]

                         

                        Set Variable [ $result ; Value:

                        Let ([

                         

                        rnd = Int(Random * 10000);

                        $start1 = Get ( CurrentTimeUTCMilliseconds );

                        $$c1 = ExecuteSQL("SELECT count(*) FROM AutoenterVSreplace WHERE replaceme = ?";"";""; rnd);

                        $stop1 = Get ( CurrentTimeUTCMilliseconds ) - $start1;

                        $start2 = Get ( CurrentTimeUTCMilliseconds );

                        c2 = ExecuteSQL("SELECT * FROM AutoenterVSreplace WHERE replaceme = ?";"";""; rnd);

                        $$c2 = ValueCount(c2);

                        $stop2 = Get ( CurrentTimeUTCMilliseconds ) - $start2];

                         

                        $stop1 & ¶ & $stop2)

                         

                        Set Variable [ $sqlcount ; Value: $sqlcount + GetValue($result;1) ]

                        Set Variable [ $valuecountofsql ; Value: $valuecountofsql + GetValue($result;2) ]

                        If [ Mod($i; 500) = 0 ]

                          Set Variable [ $$data ; Value: $$data & $i & Char(9) & $sqlcount & Char(9) & $valuecountofsql & Char(9) & $$c1 & " " & $$c2 & ¶ ]

                          Refresh Window [  ]

                        End If

                        End Loop

                         

                        and this is what I'm getting with Filemaker 14:

                        • 9. Re: ExecuteSQL Count
                          beverly

                          COUNT(*) uses ALL fields/columns in the query and this can be much slower than specifying a field (typically the PrimaryKey field) to count - as it will be non-blank.

                           

                          I also discourage the use of COUNT(*) if a query also has other fields, as EACH field/column then must be in any ORDER BY clause.

                           

                          just my 3¢ and YMMV (or YKMV)

                          beverly

                          • 10. Re: ExecuteSQL Count
                            siplus

                            A combo of quick find and get(foundCount) beats 'em all, anyway...

                            • 11. Re: ExecuteSQL Count
                              user19752

                              You seem confusing SELECT * with COUNT(*) some while...

                              COUNT(*) is special syntax for getting "row count" in result set.

                              • 12. Re: ExecuteSQL Count
                                cortical

                                Hi Beverly,

                                 

                                not quite following that.

                                are you indicating that with *,  to include a row in the row count all fields in that row would be evaluated as null/not null?  and if at least one was not null then it would rate as a row?  i.e. the way the count engine is actually working as opposed to the nature of the result?

                                 

                                w3schools:

                                The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

                                The COUNT(*) function returns the number of records in a table:

                                 

                                 

                                oracle:

                                COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.

                                If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

                                If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.

                                If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.

                                 

                                 

                                ==

                                My target table is small c.100, and the result set is only ever going to be on the order of 100. But that is only a circumstance in this instance. 

                                 

                                .

                                • 13. Re: ExecuteSQL Count
                                  beverly

                                  No, sorry no confusion between SELECT * and SELECT COUNT(*) - I don't like to do either. My preference for the reasons I stated.

                                   

                                  I can get 'row count' by using the Primary Key field for a COUNT(myfield_pk), because the primary key should never be empty.

                                   

                                  beverly

                                  • 14. Re: ExecuteSQL Count
                                    beverly

                                    you are correct, COUNT() in SQL (not just FileMaker's ExecuteSQL function ) returns not NULL rows. I'm saying I don't use COUNT(*) - note the wildcard. I specify the "id" field/column (primary key) in the row to count, unless I really need to know how many in another column is not NULL, rather than the single wildcard which implies all columns are counted.

                                     

                                    YMMV, YKMV

                                    beverly

                                    1 2 Previous Next