SQL in Calculated Fields
SQL Frames offers several standard SQL functions such as COALESCE , CASE WHEN
and others available via the SQL JavaScript object along with all the standard SQL constructs.
Using these functions makes it possible to generate valid SQL. When calculated fields are
created using SQL.script, the script expression is directly substituted into the SQL and
hence the resulting SQL may not be valid especially since any valid JavaScript is permitted
in the script field. If SQL Frames is being used primarily as a compute engine, then whether
a valid SQL can be generated or not doesn't matter. However, if it is used to also generate valid SQL,
then it is important to make sure to limit the usage of the script fields to a very restrictive
set of expressions that the target database can interpret (mostly simple arthmetic functions).
There will be times when the SQL functions themselves may have to be combined with simple arthemetic expressions. Expressing the logic completely as JavaScript will do computation but not valid SQL generation. To solve this problem, SQL Frames provides the concept of local variables for scripts. Below is an example.
While local variables are powerful to use the SQL Frames in-memory analytics engine to do computation and also be able to generate valid SQL, care should be taken to avoid problems such as SQL injection since the code in the script variables is directly substituted into the generated SQL.