AnsweredAssumed Answered

ExecuteSQL Bug

Question asked by tomr303 on Jun 19, 2018
Latest reply on Jun 19, 2018 by Vincent_L

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

Product Version

FileMaker Pro 17 Advanced v17.0.1.143, and

FileMaker Pro 16 Advanced v16.0.5.500

OS and version

macoOS 10.13.5

Browser - n/a

Hardware

MacBook Pro 13-inch 2017 and

iMac 27' 2017

Description:

ExecuteSQL SQL query fails to return all matching values

How to replicate

Unknown; occurs only with specific data set.

Workaround (if any)

Use UniqueValues ( List ( fieldName ) )

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

 

I have two different scripts that are having a problem where ExecuteSQL fails to return all matching values. Both queries are to single tables (no joins) and return unique values of a single field, based on the search criteria. These scripts  - and the SQL queries - have been in use and functioning properly for close to 4 years. But in the last few months, the the SQL queries are failing to return all expected values. The returned values are from two separate fields, but both are similar - calculation fields containing concatenated text from several other fields. I've isolated the values that are not being returned. When I changed the script step to use UniqueValues ( List ( fieldname ) ) all expected values were returned.

 

I found a discussion at FMS 15v3 huge issue : Client-Server Cache gets ... | FileMaker Community that concerned a corrupted cache. But deleting the cache as described did not solve my problem.

 

I'm showing one of the SQL queries below. If it would be helpful, I would be glad to share my file.

 

Thanks,

Tom RuBane

 

----------

 

// Built by SQLExplorer.  Compliments of SeedCode… Cheers!

 

Let ( [

 

// Define Carriage Return Substitution Character

ReturnSub = "\n" ;

 

// Enable the second line here if you want the header in your results

 

header = "";

//header = "a.trancheConsolidation__c";

 

// Define Table variables

aORI = Quote ( GetValue ( Substitute ( GetFieldName ( ORI::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

 

// Define Field Variables

aid_unit = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( ORI::_id_unit ) ; "::" ; ¶ ) ; 2 ) ) ;

atrancheConsolidation__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( ORI::trancheConsolidation__c ) ; "::" ; ¶ ) ; 2 ) ) ;

 

// Build SQL Query

q =

"SELECT DISTINCT " & atrancheConsolidation__c & "

FROM " & aORI & "

WHERE " & aid_unit & " = ? " ;

 

// Run SQL Query

result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$UnitID_current ) ] ; 

 

// Clean up carriage returns

List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

Outcomes