6 Replies Latest reply on Feb 7, 2015 9:40 AM by wimdecorte

    Execute SQL - Comparing Strings with =

    madmike6537

      Sorry for all the SQL questions lately, I have a simple one and I just cant figure it out. I have a field that stores the name of a color selected from a value list like Blue, Green, Yellow, ect.

       

      I am trying to do some conditioning formatting based on an SQL statement. In my statement, I check what the value of the ColorCode field is for a specific technicians name - but its returning ALL the colors instead of just one. Here is a slimmed down version of the statement that is causing the error:

       

      ExecuteSQL("SELECT ColorCode FROM STAFF WHERE Staff_Name = ?"; ""; ""; STAFF::Staff_Name)

       

      No matter what I put as the parameter, it returns all the possible colors. I can even just put a letter "a" as the parameter and it returns all the colors.

       

      It seems to me there is an issue with trying to compare strings using = , but when I checked W3 site on SQL it seems this should work fine. What am I missing?

        • 1. Re: Execute SQL - Comparing Strings with =
          wimdecorte

          are you on a layout based on the STAFF table occurrence for this conditional format?

          • 2. Re: Execute SQL - Comparing Strings with =
            beverly

            SQL comparisons are CASE-SENSITIVE. "a" != "A". You can sometimes get around this:

             

            "SELECT ColorCode

            FROM STAFF

            WHERE LOWER(Staff_Name) = ?"

             

            ""; "" ; Lower(Staff::Staff_Name)

             

             

            Yes the functions in SQL and FM are named the same and perform the same action. I used lower here, but upper would accomplish the same thing. Perhaps that helps?

             

            Beverly

            • 3. Re: Execute SQL - Comparing Strings with =
              madmike6537

              Hello, thanks for the reply. I am not on a layout based on the STAFF table. My example might have been confusing because I slimmed down the parameter part. The parameter is being put in there by another SQL statement which returns a single "staff name". I have tested that part and it works without any issues.


              You could replace the parameter with "steve" in my example.

              • 4. Re: Execute SQL - Comparing Strings with =
                madmike6537

                Hi Beverly,

                 

                Thanks as always for the reply. It doesnt seem to be an issue with case because its returning too many results, rather than no results. In fact it seems that no matter what string I pass in the parameter, all the possible color options are returned.

                 

                So to further my example, I have two records in my staff table, one has the staff name "preston" and color "blue", the other has the staff name "steve" and color "orange".

                 

                When I run this execute sql statement, the results is:

                Orange

                Blue

                 

                 

                ExecuteSQL("SELECT ColorCode FROM STAFF WHERE  Staff_Name = ?"; ""; ""; "preston")

                 

                When I run the below SQL statement, the results are also:

                orange

                blue

                 

                ExecuteSQL("SELECT ColorCode FROM STAFF WHERE  Staff_Name = ?"; ""; ""; "p")

                 

                It seems no matter what I put in there, it returns all the possible results.

                • 5. Re: Execute SQL - Comparing Strings with =
                  madmike6537

                  Lol - wow I feel like deleting this post now. I must have hit my scroll wheel somehow and changed my Staff_Name field to a number field some how.

                   

                  Time to get some more sleep I guess.

                  • 6. Re: Execute SQL - Comparing Strings with =
                    wimdecorte

                    madmike6537 wrote:

                     

                    I am not on a layout based on the STAFF table.

                     

                    ExecuteSQL() itself is context independent, but not the parameters that you pass into it like you do.  If you reference a field then that field will be evaluated from the context that you happen to be on in order to try and read its value.