2 Replies Latest reply on Sep 6, 2012 7:58 AM by Shauniedarko

    ExecuteSQL and Case Sensitive WHERE

    Shauniedarko

      So I'm playing around with the ExecuteSQL script step and it's great. I've been using SeedCode's SQL Explorer to get the basics. I'm curious though if there's a way around the case sensitivity of the Where statement without having to join multiple Where statements together.

       

      For example, I'm using a ExecuteSQL to query an unrelated, external table, and build a virtual list of Company Names. It works great. I set a search field that fills in the Where statement and it also works fine. The problem comes because the WHERE statement is case sensitive. So if I'm looking for Miami and the company name was entered as MIAMI or MIami or any of the many variations other than Miami, it won't find the record. Is there any way to get around this? The Query looks like this:

       

      // ------------ BEGIN EXECUTESQL BLOCK ------------

       

      Let ( [

       

      ReturnSub = " " ; // We need to swap out carriage returns in your results with a different character, so specify the character here.   is the default.

      SQLResult = ExecuteSQL (

       

      // ------------ BEGIN QUERY ------------

       

      "SELECT a."CompanyRecID", a."CompanyName"

      FROM "aACE_Companies" a

      WHERE a."CompanyName" LIKE ?

      ORDER BY a."CompanyName" ASC" ;

       

      // ------------ END QUERY ------ ------

       

      // ------------ BEGIN FIELD AND ROW SEPARATORS ------------

       

      "|" ; "|*|" ;

       

      // ------------ END FIELD AND ROW SEPARATORS ------------

       

      // ------------ BEGIN ARGUMENTS ------------

      // ------------ These arguments are pulled from the values you entered when running your query. You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

       

      $$CompanySearch

       

      // ------------ END ARGUMENTS ------------

       

      ) ] ;

       

      // ------------ BEGIN CARIAGE RETURN SUBSTITUTIONS ------------

       

      Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )

       

      // ------------ END CARIAGE RETURN SUBSTITUTIONS ------------

       

      )

       

      // Compliments of SeedCode… Cheers!

       

      // ------------ END EXECUTESQL BLOCK ------------

       

       

      Thanks!

       

      S