1 2 Previous Next 15 Replies Latest reply on Mar 11, 2017 4:10 PM by beverly

# How to right a recursive custom function to do some math on an array

need help writing or finding a custom function to do some math on a list of values gotten from an ExecuteSQL calculation.

list looks like this ....

5,4

5,8

1,11

6,6

want to walk through the list and multiply the two value together and then sum the results together ... the number of list values can vary.

eg. (5*4) + (5*8) + (1*11) + (6*6)  = 107 .

Thanks all

• ###### 1. Re: How to right a recursive custom function to do some math on an array

ExecuteSQL(

>     " SELECT '('|| column1, column2 ||')'

>          FROM .... "

>     ; "*" ; "+" )

then you will have the 'string' which can be 'solved' with

Evaluate()

beverly

1 of 1 people found this helpful
• ###### 2. Re: How to right a recursive custom function to do some math on an array

This case ( and ) are not needed, so the columns can be aggregated value like SUM(column1)

1 of 1 people found this helpful
• ###### 3. Re: How to right a recursive custom function to do some math on an array

wow!  cool.  Thanks Beverly

• ###### 4. Re: How to right a recursive custom function to do some math on an array

beverly

Such an excellent reply. The only confusing part is all the quoting delimiters necessary, but hey, it works!

Here's a working example using the OP's data:

Final Result:

The window without the leading Evaluate is just what you would expect:

Of course, it worked perfectly!

• ###### 5. Re: How to right a recursive custom function to do some math on an array

Changing the delimiters gives us a few possibilities not otherwise possible with ExecuteSQL().

And we have to use Evaluate() - another FM function to get the result.

There are alternatives that might involve a calculation field

prod_c = col1 * col2

and a summary field

totProd_s = Total of prod_c

There are methods to loop the records and store into variables.

So many tools in the box...

Sent from miPhone

• ###### 6. Re: How to right a recursive custom function to do some math on an array

Do you know where the delimiters available (like "||") are documented?

I can't seem to find them.

Thanks.

• ###### 7. Re: How to right a recursive custom function to do some math on an array

Yes.

|| or +

Are concatenation in SQL strings.

The delimiters of which I speak are the 2nd & 3rd parameters of the eSQL function. They need not be the defaults of

; "" ; ""

Which are normally the TAB - Char(9) - to separate the columns output and the CR - Char(13) - to separate each row of the result output.

Using

; "*" ; "+"

as I did turned the text result into a math equation that could be Evaluated.

In theory the "(" & ")" were not needed if Order of precedence applied. I rarely leave that to chance. This carbon-based computer does not trust the silicon-based ones. (Did I say that out loud?)

Or did you mean something else?

beverly

Sent from miPhone

• ###### 8. Re: How to right a recursive custom function to do some math on an array

You can also use + as a concatenation operator.

But it as has already been pointed out, the parenthesis do not change how this expression evaluates. You can simplify the query by omitting them. (My Dear Aunt Sally.... ;-) )

This is nonetheless a classic example of why I think of ExecuteSQL as "the list function on steroids".

• ###### 9. Re: How to right a recursive custom function to do some math on an array

Looking at this posting again, ....Why use EVALUATE and the delimiter gymnastics when you can just execute what the OP wants using ExecuteSQL -- directly?

Am I missing something?

I guess Evalutate() would be needed if the data were text and not numeric, but that, to me, points to a database field type problem possibly.

• ###### 10. Re: How to right a recursive custom function to do some math on an array

I updated my posting to ask why use Evaluate() at all? At as least as I understood the OP's posting and your intiial reply.

Thanks

• ###### 11. Re: How to right a recursive custom function to do some math on an array

Yes "+" is SQL concatenation. However, as this was a math expression (as text result), I thought "||" was preferable in this query.

Sent from miPhone

• ###### 12. Re: How to right a recursive custom function to do some math on an array

I was unable to test and feared that

SUM( col1*col2 )

would be one of those things that failed. I know there are some nested SQL functions that cannot be used with SQL aggregate functions (in ExecuteSQL).

Sent from miPhone

• ###### 13. Re: How to right a recursive custom function to do some math on an array

Yes, the calc works fine, without Evaluate(), as long as the fields are both numeric.

Sadly, if the fields are text you get a data type error (of course, sadly, ExecuteSQL just says: "?" if the fields aren't numeric -- Execute SQL makes no effort to convert or auto-box the values.)

Happily, as shown above, in MySQL, the same query works whether the field types are int or varchar.

Thanks Bev.

• ###### 14. Re: How to right a recursive custom function to do some math on an array

SUM(a*b) works but SUM(a)*SUM(b) doesn't on FM.

So this fails

ExecuteSQL ("

SELECT '(' || SUM(col1),SUM(col2) || ')'

FROM SQLtest

GROUP BY col3

";"*";"+")

1 of 1 people found this helpful
1 2 Previous Next