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[2];
$value[$n]
)
];
//Substitute ( $sqlQuery ; " " ; "" ) &¶& // sql preview
If ( $sqlResult = "?" ;
Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
$sqlResult
)
)