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.