6 Replies Latest reply on Feb 23, 2016 9:29 PM by beverly

    SQL count querry

    markpelleymounter

      I am very new to (trying) to build ExecuteSQL statements and have already run into a stumbling block.

       

      I have the following statement:

       

       

      ExecuteSQL ( "SELECT COUNT (*)

       

      FROM Clients

      WHERE \"isactive\" = ?

      AND \"Rep Name\" =? "

      ;   ""  ; "" ; "1" ; Clients::globaldisplayname )

       

      It works, okay expect that the Rep Name field could have, for example "Tom Jones / Gary Smith" or just "Tom Jones"

       

      If I am logged in as Tom Jones, I want the result to include all result that include my name.  As it is, the sql result only include exact matches.

       

      Is there a quick way to have the results be all those that contain "Tom Jones" not just exact matches?

        • 1. Re: SQL count querry
          beverly

          SQL does not do exact matches. The searches are case-sensitive (Tom ≠ tom).

           

          You should research the use of LIKE in SQL queries. There may be a heavy penalty and using something other than SQL may be advantageous.

           

          WHERE myField LIKE  UPPER(?)

          ...

          ; "*" & Upper(myField) & "*"

           

          beverly

          • 2. Re: SQL count querry
            coherentkris

            I agree with Bev that LIKE is a contender for a solution but it may come with a performance hit.

            I would also say that Count(*) is not optimal as it will retrieve all columns from the Clients table and then count them.. What you should do is count on only one col for count i.e.

            ExecuteSQL ( "SELECT COUNT (primarykey_col)

             

            FROM Clients

            WHERE \"isactive\" = ?

            AND \"Rep Name\" =? "

            ;   ""  ; "" ; "1" ; Clients::globaldisplayname )

             

            I would go one step further and say that the field name "Rep Name" is an opportunity for improvement as, if i understood right, it contains none or many representative names.

            Good rule that i try to follow is dont name fields with a name that indicate singular when it can contain multiples.

            Rep Name could be Rep Names or some such that clearly show its not single rep name.

            • 3. Re: SQL count querry
              IanJempson

              You'll find that "SELECT COUNT(*)" is quite a bit faster than "SELECT COUNT(primary key_col)".

               

              the SQL engine knows that you're after just the count so it doesn't return any rows.

              • 4. Re: SQL count querry
                Mike_Mitchell

                coherentkris wrote:

                 

                Good rule that i try to follow is dont name fields with a name that indicate singular when it can contain multiples.

                Rep Name could be Rep Names or some such that clearly show its not single rep name.

                 

                Slightly off-topic, but I would go further and suggest that you separate your reps out into a different table if the possibility exists that more than one may be present.

                • 5. Re: SQL count querry
                  user19752

                  "*" may be typo of "%", wild card for LIKE.

                  • 6. Re: SQL count querry
                    beverly

                    Yes! good catch. I placed the incorrect wildcard:

                     

                    LIKE '%something%'

                    http://www.w3schools.com/sql/sql_like.asp

                     

                    beverly