7 Replies Latest reply on Jun 14, 2017 10:02 AM by JamesPeragine

    Group comparisons in ExecutiveSQL statement

    JamesPeragine

      New to SQL so please forgive me if this is an obvious question.

       

      If you have a lot of conditions in an ExecuteSQL statement.  Is there a way to group certain comparisons keys that will be in every OR condition

       

      example:

       

      ExecuteSQL (" SELECT \"_pk\" FROM table WHERE

      SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND SkyColorSort = SkyColor OR

      SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND WallColorSort = WallColor OR

      SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND FloorColorSort = FloorColor OR

      SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND WallShapeSort = WallShape "; ""; "")

        • 1. Re: Group comparisons in ExecutiveSQL statement
          beverly

          Use the parenthesis:

          ( a AND b AND c )

          OR

          ( d AND e AND f )

          OR

          g

          OR

          ( h AND i )

           

          Sent from miPhone

          1 of 1 people found this helpful
          • 2. Re: Group comparisons in ExecutiveSQL statement
            user19752

            4 ORs all have 2 ANDs, then

            SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND

            (

            SkyColorSort = SkyColor OR

            WallColorSort = WallColor OR

            FloorColorSort = FloorColor OR

            WallShapeSort = WallShape

            )

            2 of 2 people found this helpful
            • 3. Re: Group comparisons in ExecutiveSQL statement
              JamesPeragine

              Awesome. I had a feeling it had to do with paranthasis but wanted to confirm with the genius squad.  Thanks so much for the help guys it is much appreciated.

               

              I have been implementing ExecutiveSQL to count and establish my portal relationships using lists.  I have noticed a substantial increase in speed and performance.  I have read that the speeds should be comparable to native FM but so far this method seems far superior.

              • 4. Re: Group comparisons in ExecutiveSQL statement
                philmodjunk

                Are you replace a portal filter with a list produced by your ExecuteSQL query?

                 

                If so, then you are comparing apples to oranges as Relationship level filtering (with your return separated list) is generally more efficient than a portal filter.

                • 5. Re: Group comparisons in ExecutiveSQL statement
                  JamesPeragine

                  Yes I am.  My SQLStatement creates a list of ID's with I compare agains my ID in the portal relationship.  What used to take almost 10 seconds to update is now almost instantaneous. 

                   

                  I'm then able to sort the portals using a calculation field in the table that determines where the key is in the list.  Here's the calc

                  ValueCount ( Left ( _fkSQLFilter ;Position (  "¶"& _fkSQLFilter & "¶" ; "¶" & _pkLeads & "¶" ; 1 ; 1 )  + Length ( _pkLeads ))  )

                  _fkSQLFilter is obviously the List field that the SQL Statement creates and _pkLeads is the Primary Key.

                   

                  I then installed a timer that counts the records in the portal every 30 seconds and compares them against the last count. If the count is different it rebuilds the list again to update the portal.

                   

                  It's impressive how much faster this method is. 

                  • 6. Re: Group comparisons in ExecutiveSQL statement
                    philmodjunk

                    As I said before, it's not that ExecuteSQL is fast, it's that a return separated list of values as your match field is much faster than a portal filter expression. With a portal filter expression, you basically have an unstored calculation that has to evaluate once for every related record in order to determine which will be allowed to appear in the portal. Your ExecuteSQL expression on the other hand, only has to evaluate once. (There are a number of other techniques that could be used to create a return separated list of values for the relationship and once they've put that list into the match field, you'd see the same improvement in how fast the portal updates.)

                    • 7. Re: Group comparisons in ExecutiveSQL statement
                      JamesPeragine

                      Got it and good to know.   That makes sense actually.  Thanks for explaining