1 2 Previous Next 15 Replies Latest reply on Sep 27, 2016 7:48 AM by sreese

    SQL Question

    sreese

      I am working on a SQL query and FileMaker is doing something that I wouldn't expect.

       

      This is the SQL query that I built that works.

       

       

      Substitute ( ExecuteSQL ( "Select display_html from fileTypes where extension = ? fetch first row only" ; ""; "" ; Lower ( "." & nCaseDocumentVersions::file_extension) ); ["|location|" ; nCaseDocumentVersions::file_path & nCaseDocumentVersions::file_name];["¶";""])

       

      So a little background, I am working on a solution that uploads and parses files to the server and I have an acceptable extension list. The above SQL query without the fetch first row only returns 2 results when it should only return one.

       

      Example - searching for XLSX should only return XLSx but it returns both XLS and XLSX with the SQL query. Additionally just xls will return both as well. This doesn't make sense because I didn't use the like command I used the = command.

       

      In every other form of SQL under the sun besides FileMaker apparently that would only return an exact match.

       

      What on earth am I missing here? Its FMPA 32 bit on Windows 10 running on a 14 server patched to the latest edition.

       

      Thanks for any input.

       

      Scott

        • 1. Re: SQL Question
          fmpdude

          Could you post a copy of your solution or a subset of it that is not proprietary? Difficult to see what's going on by your query alone.

           

          You might try a third party tool like RazorSQL and connect to your live FMP solution. RazorSQL (and similar tools) give you ACTUAL ERROR MESSAGES (not the unhelpful "?") and even SQL code assist. Using that tool or a tool like it, I find query formulation and debugging go much faster.

           

          These external tools also use regular SQL so you can skip the ExecuteSQL syntax until you get the query working. Then just go back to FMP and add your query back there, and update the replaceable parameters and such.

           

          Viola!

          • 2. Re: SQL Question
            David Moyer

            Hi,

            it seems that the extension should be contained in quotes.

            • 3. Re: SQL Question
              sreese

              Sure Here is a layout without all of the fields on it:

              Here is the table structure

               

              And here is an example of the file types records

               

              So the query checks the field file extension against the last fileTypes table for a match. It's selecting a field that chooses which HTML code to display.

              • 4. Re: SQL Question
                sreese

                I've never been able to get the queries to work right with quotes in FileMaker. I've always had to make them sans quotes or they don't work.

                • 5. Re: SQL Question
                  David Moyer

                  hmm, but doesn't the dot, as in .wav make wav look like a field name without a table?

                  If you could isolate the sql in a variable and view that variable, it might be helpful.

                  • 6. Re: SQL Question
                    beverly

                    the column extension in the query, if a reserved word can be escape quoted:

                    \"extension\"

                    I don't think that is the problem, but it cannot hurt to do this.

                     

                    if the value passed as a parameter is in the field (and concatenated is ok here) then the ExecuteSQL will apply the correct single quote to the text value before the query is evaluated.

                    WHERE extension = '.tiff'

                     

                    I've rewritten the query like this and used the $vars so that this can be monitored in Data Viewer, if you have it or passing the $var values to a custom dialog if not.

                     

                    Let (

                     

                      [ $query = "

                           SELECT display_html

                           FROM fileTypes

                           WHERE \"extension\" = ?

                           FETCH FIRST ROW ONLY

                           " // query to be made

                     

                      ; $result = ExecuteSQL (

                           $query ; "" ; ""

                           ; Lower ( "." & nCaseDocumentVersions::file_extension )

                           ) // query called with one parameter

                     

                      ]; If ( $result = "?" ; ""

                           ; Substitute

                                ( $result

                                ; ["|location|" ; nCaseDocumentVersions::file_path & nCaseDocumentVersions::file_name]

                                ; [Char(13) ; ""]

                                ) // end Subs to be run after the query result is returned

                           ) // end if

                     

                      ) // end LET

                     

                    the use of Lower() in passing the parameter should match EXACTLY what you have passed.

                    xls => xls or XLS, but not xlsx or XLSX (from the field)

                    xlsx => xlsx or XLSX (from the field)

                     

                    there should be only one result returned IF you have ONLY one record with the extension in fileTypes.

                     

                    My question: where are you calling the query: from "nCaseDocuments" or "nCaseDocumentVersions" ?

                    is this scripted, auto-enter (field definition) or ???

                    beverly

                    • 7. Re: SQL Question
                      sreese

                      The "." is added because it is in the second table and not in the first. Its for file check on uploading.

                       

                      I am performing the query from nCaseDocuments and uses through the relation to version.

                      • 8. Re: SQL Question
                        beverly

                        David, are you thinking, possibly, because the value/contents of the field 'extension' has

                        ._____

                        that SQL will confuse it with a column name (without the table)?

                         

                        I know the '.' is an operator/wild card for regEx, but not in FileMaker symbols/operators, so that should also not be a problem (and the query is not the standard FIND)

                        Refining find requests in FileMaker Pro using find operators | FileMaker

                        • 9. Re: SQL Question
                          beverly

                          sreese, are you sure there is ONE-to-ONE relationship for a single value to come over from nCaseDocumentVersion? might there possibly be more than one related record? put the data into a portal with more than one row to see. or GTRR (go to related records) from nCaseDocuments to verify

                           

                          or don't perform the query if

                          Count ( nCaseDocumentVersion::id ) ≠ 1

                           

                          beverly

                          • 10. Re: SQL Question
                            David Moyer

                            it's definitely not a problem if FM encases the variable ? with single-quotes.  That's a cool feature.

                            • 11. Re: SQL Question
                              sreese

                              Its a one to many relationship that only returns the newest record.

                              • 12. Re: SQL Question
                                beverly

                                sort (descending) in the relationship graph? (we cannot see the relationship dialog, to verify)

                                beverly

                                • 13. Re: SQL Question
                                  sreese

                                  Here ya go:

                                  • 14. Re: SQL Question
                                    beverly

                                    Thank you. Then it SHOULD pull the correct information. I might still make the call from the related version table and view it in the parent table. Then there is sure to be one result.

                                     

                                    beverly

                                    1 2 Previous Next