4 Replies Latest reply on Feb 26, 2015 8:36 AM by twelvetens

    ExecuteSQL Query....

    twelvetens

      Hi There,

       

      Struggling (again!) with building my ExecuteSQL syntax.

       

      In 'traditional' filemaker relationship definitions, I can use a carriage returned list of text values as the many side of a many-one relationship. Thus on on side, I can have:

       

      Option A

      Option B

      Option C

      Option D

       

      And this will create a relationship with any joined tables as long as the match field caontains one (or many) of the Options A-D.

       

      I'm struggling to re-create this matching using the WHERE segment of an ExecuteSQL clause. I'm trying this:

       

      Let (

      [

      id = Items::items id ;

      weight = weight in kg ; // weight is a number field

      dic = user selected dispatch countries ; // a carriage returned list of text values.

      so = user selected shipping options ; // a carriage returned list of text values.

      dec = user selected destination countries // a carriage returned list of text values.

      ]

      ;

      ExecuteSQL (

       

       

      "SELECT " & SQLfield ( scenario planner::_scenario planner id )

      & " FROM " & SQLTable ( scenario planner::_scenario planner id )

      & " WHERE " &

      SQLfield ( scenario planner::item id ) & " = " & id

       

      & " AND " &

      SQLfield ( scenario planner summary::max size ) & " ≥ " & weight

       

       

      & " AND " &

      SQLfield ( scenario planner summary::dispatch scenario planner country ) & " = " & dic

       

       

      & " AND " &

      SQLfield ( scenario planner summary::scenario planner shipping option ) & " = " & so

       

       

      & " AND " &

      SQLfield ( scenario planner summary::destination country ) & " = " & dec

       

       

      & " AND " &

      SQLfield ( scenario planner summary::match on format ) & " >0 "

      */

       

       

      & " ORDER BY " & SQLfield ( scenario planner::sort order )

      & " FETCH FIRST 3 ROWS ONLY"

      ;

       

       

      "" ; "" )

       

       

      //Close Let

      )

       

      But the match doesn't seemt to work - HELP - any takers????

        • 1. Re: ExecuteSQL Query....
          TimDietrich

          James --

           

          For the conditions that you're using for dic, so, and dec, you could use an "IN" predicate.

           

          The caclulcation might look like this:

           

          Let (

          [

          id = Items::items id ;

          weight = weight in kg ; // weight is a number field

          dic = user selected dispatch countries ; //  a carriage returned list of text values.

          so = user selected shipping options ; //  a carriage returned list of text values.

          dec = user selected destination countries //  a carriage returned list of text values.

          ]

          ;

          ExecuteSQL (

          "SELECT " & SQLfield ( scenario planner::_scenario planner id )

          & " FROM " & SQLTable ( scenario planner::_scenario planner id )

          & " WHERE " &

          SQLfield ( scenario planner::item id ) & " = " & id

          & " AND " & SQLfield ( scenario planner summary::max size ) & "  ≥  " & weight

          & " AND " & SQLfield ( scenario planner summary::dispatch scenario planner country ) & " IN ( '" & Substitute ( dic; ¶; "', '") & "' )" &

          & " AND " & SQLfield ( scenario planner summary::scenario planner shipping option ) & " IN ( '" & Substitute ( so; ¶; "', '") & "' )"

          & " AND " & SQLfield ( scenario planner summary::destination country ) & " IN ( '" & Substitute ( dec; ¶; "', '") & "' )"

          & " AND " & SQLfield ( scenario planner summary::match on format ) & " >0 "

          & " ORDER BY " & SQLfield ( scenario planner::sort order )

          & " FETCH FIRST 3 ROWS ONLY"

          ;

          "" ; "" )

          )

           

          -- Tim

          • 2. Re: ExecuteSQL Query....
            twelvetens

            Tim, you my friend, are an absolute BLOODY LIFE SAVER.

             

            The time you have saved me is imeasurable.

             

            THANKYOU SO MUCH!

             

            James.

            • 3. Re: ExecuteSQL Query....
              TimDietrich

              James --

               

              Glad to hear that this worked.

               

              Good luck with the project!

               

              -- Tim

              • 4. Re: ExecuteSQL Query....
                twelvetens

                Hi Tim,

                 

                Not sure if you're still getting notified about this thread, but I could sure do with a little help again!

                 

                The values in the fields above are now populated in such a way that it may look like this:

                 

                Option A

                Option B

                Option C

                Option C 1

                Option C 2

                Option 4

                 

                And using the 'IN' predicate 'Option C' creates a match with all three of the "Option C", "Option C 1" and "Option C 2" related fields.

                Obviously, the tables should be using unique keys here, to avoid this, but that would take a serious amount of re-writing large amounts of the schema and scripting etc.

                 

                Is there an SQL predicate similar to 'IN' which is more precise in it's evaluation?

                 

                Here's hoping!!!

                 

                James.