7 Replies Latest reply on Apr 3, 2017 3:08 AM by beverly

    ExecuteSQL in Repeating Field

    Jason Wood

      Unexpected result when using ExecuteSQL with "OR" keyword on multiple repetitions of repeating field...

       

      ExecuteSQL ( "SELECT COUNT(*) FROM PROFILES WHERE phone[1] = '123456789'" ; "" ; "" ; "" )

       

      Result is 1 when the first repetition of "phone" field contains '123456789'

       

      ExecuteSQL ( "SELECT COUNT(*) FROM PROFILES WHERE phone[1] = '123456789' OR phone[2] = '123456789'" ; "" ; "" ; "" )

       

      Result is 1 only if the second repetition of "phone" field contains '123456789'. Otherwise the result is 0. I expect it to be 1 when the first repetition is '123456789' but it doesn't work.

       

      I tried replacing "phone[1] = '123456789'" with "nameFirst = 'Name'" and in this case I do get a result of 1 if EITHER test is true, but no such luck when both sides are the repeating field.

       

      Bug?

       

      FMA 14.0.6

       

      Basically I just want a select statement to see if a specified phone number exists in any of the 3 repeating phone number fields. Yes I realize that a portal would be better but this is what I have!;-)

        • 1. Re: ExecuteSQL in Repeating Field
          user19752

          This looks bug, but FM can't find on individual repetition, so I prefer don't use this.

           

          If you enter find mode then input find criteria in repetition[1], it finds all repetition with it. (Using SQL FM behave differently, but I guess index can't be used)

          And, if you input different value on rep[1] and rep[2], it uses them as "AND" criteria for all repetition.

          • 2. Re: ExecuteSQL in Repeating Field
            philmodjunk

            Yes I realize that a portal would be better but this is what I have

            It's not all that hard to convert a repeating field or set of repeating fields into a set of records in a related table. Import records create a new table, populate it with records created by splitting the repetitions into separate records and copy over a match field value to link them back to the original record all in one import records operation.

            • 3. Re: ExecuteSQL in Repeating Field
              beverly

              I don't consider it a bug, either!

               

              WITH repeating field:

              phone = phone[1] // assumed default

              • FIND (in any phone repeat) where the "match" is more like:

              phone[1] or phone[2] or phone[3] or ... phone[n] // OR search

              • in Find Mode, enter different values in different repeats and it becomes an AND search!

               

              WITH related field:

              • FIND (in portal row) is like an OR (but for the any parent with a related match)

              • FIND (in portal row) with new request is still an OR  (finding the parent where any of the requests match in the portal

              • the problem is that it is difficult to get an AND search with portals as you only get one row for searching in Find mode, so we GTRR to make the AND request and then GTRR back to the parent(s).

               

              IN eSQL:

              • if we specify a repeating field without the index - we get the result the same

              phone = phone[1]

              • if we specify a repeating field with an index (not 1) we get the result

              WHERE phone[2]=?

              (note, this is not standard SQL naming, as the square brackets mean something completely different in queries & there are no "repeating fields" in SQL dbs!)

              • but we know FM is "translating" what we wrote into standard FM finds (it makes you think it would just be easier to make the standard find, right?!), so this is "allowed"

               

              How to solve (with SQL and not moving repeats to portal):

              • Since we can get ONE index from a repeating field, I might use a UNION (select each repeat) which would return like an OR find, but that does not return a COUNT() - use value count.

              • Or I might use a SQL CASE within a COUNT()

               

              How to solve (without SQL and not moving repeats to portal):

              • the above "finds" and Get( FoundCount )

               

              ==== eSQL with CASE =====

              // If (

              ExecuteSQL(

              "

              SELECT COUNT( CASE

                  WHEN phone[1]='1234567890' THEN 1

                  WHEN phone[2]='1234567890' THEN 1

                  WHEN phone[3]='1234567890' THEN 1

                  WHEN phone[4]='1234567890' THEN 1

                  WHEN phone[5]='1234567890' THEN 1

                  END )

              FROM repeating_data

              "

              ; "" ; ""

              )

              // = "?" ; "" // debugging )

              =============

               

              NOT pretty, as you must create this (a text calc) with the hard-coded repeats and values then Evaluate(). OTOH, it's "OR" & it works. It could be revised to be "AND", but OMG!!

              & YPMV (your performance may vary!)

              beverly

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL in Repeating Field
                user19752

                Nice workaround!

                 

                Make a little change so that only adding some words in WHERE clause.

                WHERE CASE WHEN phone[1]='1234567890' or phone[2]='1234567890' or phone[3]='1234567890' THEN 1 END = 1

                 

                Seeing this works as expected, this issue may be a bug on SQL optimization?

                1 of 1 people found this helpful
                • 5. Re: ExecuteSQL in Repeating Field
                  Jason Wood

                  user19752 wrote:

                  WHERE CASE WHEN phone[1]='1234567890' or phone[2]='1234567890' or phone[3]='1234567890' THEN 1 END = 1

                   

                  Seeing this works as expected, this issue may be a bug on SQL optimization?

                  Interesting! Could you clarify what the "= 1" at the end is for? And could you help me add an "AND type = '1'"

                   

                  My attempt did not work (but this should make it clear what I'm trying to do):

                   

                  SELECT COUNT(*) FROM PROFILES WHERE ( CASE WHEN phone[1]='1234567890' or phone[2]='1234567890' or phone[3]='1234567890' THEN 1 END = 1 ) AND \"type\" = '1'

                   

                  Thanks!

                  • 6. Re: ExecuteSQL in Repeating Field
                    user19752

                    The CASE works like as "unstored calculation field", so put all conditions into WHEN.

                     

                    SELECT COUNT(*) FROM PROFILES WHERE CASE WHEN ( phone[1]='1234567890' or phone[2]='1234567890' or phone[3]='1234567890' ) AND \"type\" = '1' THEN 1 END = 1

                     

                    1 has no meaning so can be any value in this technique, comparing

                    CASE WHEN condition THEN value END

                    (return value or NULL on the condition)

                    and value.

                     

                    Your syntax worked on my test, if "type" is text. For number value shouldn't be quoted.

                    1 of 1 people found this helpful
                    • 7. Re: ExecuteSQL in Repeating Field
                      beverly

                      Much better!

                       

                      Sent from miPhone