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 = "
FROM ~table1 t1
JOIN ~table2 t2
ON t1.~field = t2.~field
WHERE ~field LIKE '%~value%'
ORDER BY ~field";
$sqlQuery = Substitute ( ~sql ;
[ "~table1" ; SQLTableName ( Table1::fieldName ) ];
[ "~table2" ; SQLTableName ( Table2::fieldName ) ];
[ "~field" ; SQLFieldName ( Table1::fieldName ) ];
[ "~value" ; Table::field ]
$sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ;
//Substitute ( $sqlQuery ; " " ; "" ) &¶& // sql preview
If ( $sqlResult = "?" ;
Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
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.