7 Replies Latest reply on Sep 2, 2015 9:50 AM by erolst

    execute SQL from value list

    NancyAdelman

      Hello,

       

      Table: Product

      Fields: Product ID, Category (checkbox field so more than one category can be checked.)

       

      I need to write a sql statement to find records by searching the Category field, which can have more than one item checked.

       

      What I have below only works if there is a single category in the Category Field.

       

      SELECT

      Product_ID

      FROM

      Product

      WHERE

      (Category = 'CAT001' )

       

      How do I write the statement so it will find "CAT001" even if there other Categories checked for that field?

       

      Thank you.

       

      Nancy Adelman

      Adelman Databases

      www.adelmandatabases.com

      nancy@adelmandatabases.com

        • 1. Re: execute SQL from value list
          nicolai

          Try

           

          SELECT Product_ID FROM Product

          WHERE Category LIKE '%CAT001%'

           

          The only issue is that it will match things like CAT0011 OR CAT001a

          • 2. Re: execute SQL from value list
            beverly

            Hello, Nancy! Remember that "=" in SQL means Exact Match. There is a LIKE that can be used with wildcards, but may not help here unless the values are truly unique. (LIKE '10%' will "match" 101, 10, 10000, for example)

             

            Also remember that a "checkbox" formatted field is just a text field with the 'values' in a return-delimited list. The order of the values only depend upon the order in which they were checked (and/or unchecked). So you cannot rely on the 'placement' of the value in relation to the 'placement' in the value-list itself. Value-lists (if from fields) is typically sorted alpha and value-lists (custom) are sorted by the order in which they appear in the dialog.

             

            So using the LIKE might work if you depend on a value to have a return (before and/or after).

             

                 WHERE Category LIKE '%CAT001%'

             

            But even in the "big guns" SQL dbs, the use of LIKE and wildcards can be a performance hit.

             

            Can you explain why you want to use ExecuteSQL instead of Find or FilterValues() or ??

             

            beverly

             

             

            On Sep 2, 2015, at 9:36 AM, NancyAdelman

            • 3. Re: execute SQL from value list
              NancyAdelman

              Beverly,

              Thank you for the reply.

               

              I was going to use Execute SQL to generate a list of Product IDs to put in a global field and display the records in a portal, rather than use a multi-key relationship. Now I will change my plan and still get the Product IDs, but do it, as you said, with a find.

               

              Nancy

              • 4. Re: execute SQL from value list
                erolst

                Nancy –

                 

                if you select the values to be searched for from a value list (in your case, a value list of the category field), you can Substitute() the resulting list into an SQL array, and don't have to use wild cards; e.g.

                 

                Try

                 

                ExecuteSQL ( "

                  SELECT Product_ID

                  FROM Product

                  WHERE Category IN ('" & Substitute ( categorySelectionField ; ¶ ; "','" ) & "')

                " ; "" ; ""

                )

                 

                Note the single quotes for the individual values; that's necessary when dealing with text values.

                 

                Oliver

                • 5. Re: execute SQL from value list
                  NancyAdelman

                  Thank you, Oliver.

                   

                  If I read what you wrote correctly, that would work if my selection field is a value list with multiple choices. But in this case, the search field will have only one category. It's the category field in the products table, that I'm searching, that may have multiple lines of data.

                   

                  If I misunderstood, I'm sorry.

                   

                  Nancy

                  • 6. Re: execute SQL from value list
                    beverly

                    You are correct Ms Nancy. The IN won't work unless the field contains only ONE value that appears in the comma-separated list within the parens.

                     

                    Searching the Category field with return-separated values (you can see the values if you have a copy of the field and don't format as Checkbox), would contain more than a single "word".

                     

                    Beverly

                    • 7. Re: execute SQL from value list
                      erolst

                      NancyAdelman wrote:

                      But in this case, the search field will have only one category. It's the category field in the products table, that I'm searching, that may have multiple lines of data.

                       

                      Bummer.

                       

                      But hopefully you're aware that this isn't a good data model that makes your (database) life harder not only in this scenario.