1 2 Previous Next 18 Replies Latest reply on Jul 31, 2014 12:58 PM by erolst

    Using a multi-value field in ExecuteSQL() query

    davehob

      Could anybody help with the syntax of this query (assuming it’s possible). I need to refine this query to find values where a key value is present in a multi-value field, id_RSCBlock. (This can hold any number of return-separated values - the values actually indicate Resources which are blocked (i.e. can't be booked) when id_RSC is booked).

       

      Let ( [~query = "SELECT DISTINCT r.id
      FROM SSN s, PGM p, RBK rb, RSC r
      WHERE s.id_PGM = p.id
      AND rb.id_SSN = s.id
      AND rb.id_RSC = r.id
      (and where rb.id_RSC is present IN r.id_RSCBlock)
      AND s.timestampFrom < ?
      AND s.timestampTo > ?"];

      ExecuteSQL( ~query ; "" ; "" ;GUI::TIMESTAMPTO; GUI::TIMESTAMPFROM)

       

      I believe I need an IN clause, but attempts so far have failed. I’ve tried this:

       

      AND rb.id_RSC IN (' " & substitute (r.id_RSCBlock;"¶";"','"))


      but that’s clearly wrong (can’t use field names in the Substitute), and as far as I understand I can’t use a calculation variable to reference id_RSCBlock.

      I’m still getting to grips with ExecuteSQL(), so any suggestions would be really appreciated.

       

      Dave.

        • 1. Re: Using a multi-value field in ExecuteSQL() query
          erolst

          Isn't that the same question as this one?

           

          https://fmdev.filemaker.com/message/153646

           

          Anyway …

          Dave Hobson wrote:

          but that’s clearly wrong (can’t use field names in the Substitute),

           

          No, the usage of Substitute() is syntactically correct, since you're still in Filemaker territory, building a string (the SELECT query);

          except that you cannot use the SQL field reference format, but rather have to reference the field in FM syntax.

           

          The other error here seems to be that you've forgotten to add the closing paren to the resulting string.

           

          AND rb.id_RSC IN (' " & substitute(r.id_RSCBlock;"¶";"','")) should be

           

          AND rb.id_RSC IN ('" & Substitute ( id_RSCBlock ; ¶ ; "','" ) & "')" // within a script, you need a fully qualified reference in the format TOName::fieldName

           

          Another problem may be that your keys are numbers; in that case, you must not quote the individual values in the array (only required/valid for chars).

           

          This should be more easy to read:

           

          Let ( [

            rscBlock = Substitute ( r.id_RSCBlock ; ¶ ; "','" )

            ~query = "

              SELECT DISTINCT r.id

              FROM SSN s, PGM p, RBK rb, RSC r

              WHERE s.id_PGM = p.id

                AND rb.id_SSN = s.id

                AND rb.id_RSC = r.id

                AND rb.id_RSC IN ('" & rscBlock & "')

                AND s.timestampFrom < ?

                AND s.timestampTo > ? "

              ] ;

            ExecuteSQL ( ~query  ; "" ; "" ; GUI::TIMESTAMPTO ; GUI::TIMESTAMPFROM )

          )

          1 of 1 people found this helpful
          • 2. Re: Using a multi-value field in ExecuteSQL() query
            davehob

            Thanks, erol, for your reply.  No, it's not the same question as I asked recently - similar, which is why I hoped that what I had learned there would help me here, and it has, to a point.  In that one, I had a collection of ids which would be the same for the whole query, so I was able to put it into a variable at the start.  With this one, involving a different collection of values in each record, it seems that it's not so straightforward.

             

            I'll try your suggestion now - thanks for pointing out where I've gone wrong.

             

            Dave.

            • 3. Re: Using a multi-value field in ExecuteSQL() query
              erolst

              Note my edit to my first post re the required format: r.id_RSCBlock is (probably) not your internal FileMaker field name – in a FileMaker function, you need to reference the field by its FM name.

               

              btw, I recommend building the SELECT query in a separate Set Variable[] step before using it (or even temporarily declaring a $/$$ var in the Let() ); this will allow you to view the generated SQL code in the Data Viewer for a visual inspection and spot any obvious syntax errors.

              • 4. Re: Using a multi-value field in ExecuteSQL() query
                davehob

                Thanks - I was struggling with that!  However, now that I've included the TO name, rather than the SQL reference, FM is complaining that the expression is referencing an "<Unrelated Table>".  I guess that doesn't surprise me, but, given that my whole motivation for using ExecuteSQL is the freedom from FM relationships, is there a way around it?

                • 5. Re: Using a multi-value field in ExecuteSQL() query
                  erolst

                  Grab the first list with another SQL query.

                   

                  I think you could nest the queries, as in

                   

                  SELECT …

                  FROM …

                  WHERE xyz in (  SELECT etc) 

                   

                  but that's bound to get messy, and you can easily lose track of the current context (well, I do …).

                   

                  Probably

                   

                  Let ( [

                  query1 = code for first list ;

                  list1 = ExecuteSQL ( query1 ; …

                  formattedList1 = Substitute ( list1 ; …

                  … rest as before

                  )

                   

                  is better maintainable.

                  • 6. Re: Using a multi-value field in ExecuteSQL() query
                    beverly

                    See if this forum post (not this forum) helps:

                     

                    <http://fmforums.com/forum/topic/88426-interesting-quirk-for-unrelated-field-reference-in-field-definition/>

                     

                    ExecuteSQL is context independent, but the calculation dialog is not...

                     

                     

                     

                    Beverly

                    • 7. Re: Using a multi-value field in ExecuteSQL() query
                      davehob

                      Beverly,

                       

                      Thanks for this.  To be honest, I seem to have got into this rather above my head.  I'll have to work out the example in the linked post when I have a bit more time, and erolst's suggestion as well - at first attempt, I can't make either work, but I'm sure that's down to me.  (A bit more studying of your eSQL Missing Ref. is called for, I think...)

                       

                      Dave.

                      • 8. Re: Using a multi-value field in ExecuteSQL() query
                        beverly

                        At the top of Erolst's calculation add this for every field that is "unrelated"

                         

                             gfn = GetFieldName ( unrelated::table ) ;

                        If you have more than one, obviously, it would be

                         

                             gfn2 = GetFieldName ( unrelated::table ) ;

                             ....

                         

                        something like this, if these are the fields giving you trouble

                        ---

                         

                        Let ( [

                          gfn=GetFieldName (GUI::TIMESTAMPTO)

                          ; gfn2=GetFieldName (GUI::TIMESTAMPFROM)

                         

                          ; rscBlock = Substitute ( r.id_RSCBlock ; ¶ ; "','" )

                         

                          ; ~query = "

                         

                            SELECT DISTINCT r.id

                         

                            FROM SSN s, PGM p, RBK rb, RSC r

                         

                            WHERE s.id_PGM = p.id

                         

                              AND rb.id_SSN = s.id

                         

                              AND rb.id_RSC = r.id

                         

                              AND rb.id_RSC IN ('" & rscBlock & "')

                         

                              AND s.timestampFrom < ?

                         

                              AND s.timestampTo > ? "

                         

                            ] ;

                         

                          ExecuteSQL ( ~query  ; "" ; "" ; GUI::TIMESTAMPTO ; GUI::TIMESTAMPFROM )

                         

                         

                        • 9. Re: Using a multi-value field in ExecuteSQL() query
                          user19752

                          I think he is trying to join or emulating relationship in FM, and rscBlock is not global field.

                           

                          Then,

                          (and where rb.id_RSC is present IN r.id_RSCBlock)
                          become something like

                          AND  Chr(13) || r.id_RSCBlock || Chr(13) LIKE '%' || Chr(13) || STRVAL(rb.id_RSC) || Chr(13) || '%'

                           

                          This should be slow.

                           

                          SQL don't have 'values' concept. make it one value per one record is best.

                          • 10. Re: Using a multi-value field in ExecuteSQL() query
                            davehob

                            Beverly,

                             

                            Thanks again for trying to help me - I greatly appreciate it.  However, I still don't understand what's going on here.  Specifically, the reason for defining variables (gfn and gfn2 in your example) which are then not referenced elsewhere in the expression.  (As it happens, it's RSC::id_RSCBlock that's giving me the problem, so in my case I think I need to include gfn=GetFieldName (RSC::id_RSCBlock) instead, but I still don't get why I'm not using that variable, having defined it.)

                             

                            "user19752" is suggesting that I need to restructure my data to get rid of this multi-value field, so maybe I should think along those lines.

                             

                            Regards,

                             

                            Dave.

                             

                             

                            • 11. Re: Using a multi-value field in ExecuteSQL() query
                              davehob

                              Thanks for this suggestion - it seems that going for one value per record (always good practice really?) is the way to go here - I certainly don't want to build in slowness if I can help it.

                               

                              Dave.

                              • 12. Re: Using a multi-value field in ExecuteSQL() query
                                beverly

                                I don't know what field was giving you trouble. If it's not related, why use it? If it's a 'CONSTANT' then make a query using it on a layout where it is related. Save the result to a variable, which can be used for a second query.

                                 

                                Question? What would this look like if you were trying to make a FileMaker FIND (native, not using ExecuteSQL)? That's the basis for thinking through setting up a SQL query. If it requires relationships, use JOINs. If it has multiple requests (AND, OR, NOT), use those in your WHERE clause. Think twice, query once (parody of 'measure twice, cut once').

                                 

                                 

                                 

                                -- sent from myPhone --

                                Beverly Voth

                                --

                                • 13. Re: Using a multi-value field in ExecuteSQL() query
                                  erolst

                                  Look at the attached file; it should demonstrate the basic principle of what we discussed here (and uses a join table for bookings …)

                                  • 14. Re: Using a multi-value field in ExecuteSQL() query
                                    davehob

                                    erolst,

                                     

                                    Huge thanks to you for this.  Frankly, I was on the verge of giving up, but your example came just in time.  I had a mental block about the concept of eSQL within eSQL, despite your best efforts, and Beverly's, to enlighten me.  Your example makes it very clear.

                                     

                                    This whole thing is yet another example of how patient, supportive and invaluable this forum is to people like me.  I have learned a great deal from you the past couple of days, and I'm truly grateful.

                                     

                                    Dave.

                                    1 2 Previous Next