Assistance Multiplying 2 SQL Variables in Canvas

[butchkelley]

Hello

I'm trying to multiply 2 variables together in Canvas using tiny math. My Elastic version is 7.9.

I started with this as an example (which works fine):

filters
| demodata
| var_set "val1" value=2
| var_set "val2" value=3
| var_set "result" value={math {string "multiply( " {var "val1"} ", " {var "val2"} " )"}}
| markdown "Test: " {var "result"}
| render

What I'd like to get working is this following (I simplified my real SQL for ease of troubleshooting):

filters
| demodata
| var_set "val1" value={essql query="SELECT 1+3"}
| var_set "val2" value={essql query="SELECT 1+6"}
| var_set "result" value={math {string "multiply( " {var "val1"} ", " {var "val2"} " )"}}
| markdown "Test: " {var "result"}
| render

This, however, gives me the following error:

Expression failed with the message:
[var_set] > [math] > [string] > Can not cast 'datatable' to any of
'string, number, boolean'

Any ideas what I'm doing wrong?

Thanks,
Butch

[Felix_Roessel]

You need to get the cell from the SQL result set using getCell function

[butchkelley]

Thanks for your response.

I'm sure I have the syntax incorrect because it's giving me a similar error.

filters
| demodata
| var_set "val1" value={essql query="SELECT 1+3 AS foo" | getCell "foo"}
| var_set "val2" value={essql query="SELECT 1+6 AS bar" | getCell "bar"}
| var_set "result" value={math {string "multiply( " {var "val1"} ", " {var "val2"} " )"}}
| markdown "Test: " {var "result"}
| render

[Felix_Roessel]

Are you sure that select 1+3 results In a number that is stored in the cell?
Did you checked that with debug?

[butchkelley]

The number is stored in the cell but debug shows the number in a text field. Could that be my problem? Do I need to somehow change the field to a numeric?

filters
| essql query="SELECT 1+3 AS foo"
| math "foo"
| render as="debug"

This gives:

{
   "text": "4"
}

[butchkelley]

I got this working by dropping "filters".

var_set "val1" value={essql query="SELECT 1+3 AS foo" | getCell "foo"}
| var_set "val2" value={essql query="SELECT 1+6 AS bar" | getCell "bar"}
| var_set "result" value={math {string "multiply( " {var "val1"} ", " {var "val2"} " )"}}
| markdown "Test: " {var "result"}
| render