# Assistance Multiplying 2 SQL Variables in Canvas

**URL:** <https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481>\
**Category:** Kibana\
**Created:** [November 17, 2021, 4:26pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481 "2021-11-17T16:26:15Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![butchkelley](https://avatars.discourse-cdn.com/v4/letter/b/a4c791/32.png) [@butchkelley](https://discuss.elastic.co/u/butchkelley)\
**Post date:** [November 17, 2021, 4:26pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/1 "2021-11-17T16:26:15Z")

</div>

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):

```auto
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):

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [November 17, 2021, 8:09pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/2 "2021-11-17T20:09:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![butchkelley](https://avatars.discourse-cdn.com/v4/letter/b/a4c791/32.png) [@butchkelley](https://discuss.elastic.co/u/butchkelley)\
**Post date:** [November 17, 2021, 8:44pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/3 "2021-11-17T20:44:16Z")

</div>

Thanks for your response.

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\
**Post date:** [November 17, 2021, 9:28pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/4 "2021-11-17T21:28:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![butchkelley](https://avatars.discourse-cdn.com/v4/letter/b/a4c791/32.png) [@butchkelley](https://discuss.elastic.co/u/butchkelley)\
**Post date:** [November 18, 2021, 2:13pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/5 "2021-11-18T14:13:54Z")

</div>

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?

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

```

This gives:

```auto
{
   "text": "4"
}

```

---

<div class="post-metadata">

**Author:** ![butchkelley](https://avatars.discourse-cdn.com/v4/letter/b/a4c791/32.png) [@butchkelley](https://discuss.elastic.co/u/butchkelley)\
**Post date:** [November 18, 2021, 6:50pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/6 "2021-11-18T18:50:42Z")

</div>

I got this working by dropping "filters".

```auto
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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 16, 2021, 6:51pm UTC](https://discuss.elastic.co/t/assistance-multiplying-2-sql-variables-in-canvas/289481/7 "2021-12-16T18:51:00Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.