4 of 4 people found this helpful
- is concatenate operator in FM. You need NUMVAL() to convert to number.
I don't know if the nested AVG(NUMVAL(...)-NUMVAL(...)) will work.
It may be that you need to use the concatenated version of the query:
" SELECT AVG(" & GetAsNumber(COL1)-GetAsNumber(COL2) & " FROM TableName "
But I'm not sure that gets what you want either.
2 of 2 people found this helpful
You can convert text to numbers by using CAST:
"SELECT AVG( CAST( Col1 AS DECIMAL ) - CAST ( Col2 AS DECIMAL ) ) FROM TableName"
Yes! that works well. And compares to my test with the same data and the FM calc field for
and the FM summary field of that calc (Average of)
1 of 1 people found this helpful
Meanwhile I tested your first suggestion with NUMVAL and that works fine too and is shorter than CAST
Actually, I thought "&" was the concatenate operator in FM. I've never used "-" that way.
In any case, your NUMVAL() was perfect. As I already mentioned, I didn't see it in the documentation.
that might be a typo (or the forum munged a reply-by-email again).
"+" is SQL concatenation, as is "||" - as use in ExecuteSQL().
but the formula written this way:
might be interpreted as concatenation in ExecuteSQL(), as you said the fields were text.
I don't think it would be confused by
because we are 'casting' to a number (with NUMVAL or Menno's actual CAST)
I use sql.debug myself and find it useful, but sometimes the text returned is "a syntax error has occurred" which is no better than a question mark. When some other text IS returned, it can save you a lot of time and frustration.
When I do any SQL in FM I always use JDBC with a real IDE like Intellij.
Using that method, I get:
1. Full Error Messages
2. SQL "Intellisense" as I type the query
3. Resizable panes
4. Resizable fonts
5. No ExecuteSQL syntax
Once the query works, I then move it back to FM' ExecuteSQL.
P.S. I remain hopeful that at some point, FM will have a real SQL console that approaches almost freely-available tools.
Thanks, but I avoid needing to jump through additional hoops altogether by just using a JDBC console which has many other benefits, including full error messages.
Appreciate your reply.