AnsweredAssumed Answered

Better way to pass SQL field name as a variable?

Question asked by wintertj on Jul 16, 2015
Latest reply on Jul 17, 2015 by richardd

Any FM SQL experts ever tried to pass a field name of a field in the SQL statement as a variable to a FM ExecuteSQL calculation?

 

I'm trying to create a calculation that uses ExecuteSQL and passes pieces of the SQL string as a variable. Trying to create a modular statement that can be passed different field names (because the database contains a multitude of fields that the same exact calc needs to be performed on).

 

So far, the only way I can get this to work is by making the whole thing a string inside a Let notation and then evaluating the variable created by the Let. This works but is cumbersome and verbose and has nasty escape characters and what not:

 

Let (var1 = "ExecuteSQL(\"select count(*)

               from TABLE

              where "  & RESULT_TABLE::FieldName  & " = ?\";\"\"; \"\"; 55133)"  ;Evaluate(var1) )

 

I was hoping that FM would allow the ? question marks to function as placeholders for other parts of the SQL statement other than just the SQL parameters in the where clause (i.e. as part of the statement), but this doesn't seem to work:

 

ExecuteSQL("select count(*)

               from TABLE

              where ? = ?"; ""; ""; RESULT_TABLE::FieldName; 55133)

 

In the above, the first ? question mark isn't substituting a SQL value, but part of the SQL statement itself. This is apparently a no go?

Outcomes