Because SQL within FileMaker requires learning at least a subset of the SQL-92 standard a developer is moving beyond FileMaker's list of functions.
This page is currently a placeholder for the discussion of adopting a best practice regarding dealing with using SQL within FileMaker calculation.
Further discussion about this topic can be found on the Google Groups discussion about ExecuteSQL
Current proposed snippet which uses Substitution method (similar to printf)
Let ( [ ~sql = " SELECT t1.~field FROM ~table1 t1 JOIN ~table2 t2 ON t1.~field = t2.~field WHERE ~field LIKE '%~value%' AND ~field=? ORDER BY ~field"; $sqlQuery = Substitute ( ~sql ; [ "~table1" ; SQLTableName ( Table1::fieldName ) ]; [ "~table2" ; SQLTableName ( Table2::fieldName ) ]; [ "~field" ; SQLFieldName ( Table1::fieldName ) ]; [ "~value" ; Table::field ] ); $sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ; $value; $value; $value[$n] ) ]; //Substitute ( $sqlQuery ; " " ; "" ) &¶& // sql preview If ( $sqlResult = "?" ; Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) ); $sqlResult ) )
In order to see any SQL errors, the above calculation must be used within FileMaker's Data Viewer. The SQLDebugResult custom function (below) is very simple and was a hidden "feature".
If ( sql = "?" ; "" ; sql ) //If passing in ExecuteSQL results in an error, return empty so the error will be returned within the Data Viewer.