# Errors using query inside sub-expression

**URL:** <https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [September 20, 2020, 4:55pm UTC](https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265 "2020-09-20T16:55:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![devbot](https://avatars.discourse-cdn.com/v4/letter/d/9de0a6/32.png) [@devbot](https://discuss.elastic.co/u/devbot)\
**Post date:** [September 20, 2020, 4:55pm UTC](https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265/1 "2020-09-20T16:55:32Z")

</div>

I'm on Kibana 7.4

I'm trying to understand how to use queries inside sub-expressions. In canvas I have a markdown element that works perfectly fine with:

```auto
    filters group="default"
    | essql 
      query="select SUM(foo) as foo_total from \"index*\" "
    | head 1
    | if { context | head 1 | getCell "foo" | lte 100 } then={markdown "True" | render} else={markdown "False" | render}

```

But this doesn't work:

```auto
    filters group="default"
    | essql 
      query="select SUM(foo) as foo_total from \"index*\" "
    | head 1
    | if { context | head 1 | getCell "foo" | lte {essql query="select count(*) from \"index2*\" " | head 1 | getCell "count"} } then={markdown "True" | render} else={markdown "False" | render}

```

I get the error "Expression Failed with the following message: [if] \> [lte] \> Can not cast number to any of filter"

What's an example of nesting my query inside the if then logic here that would actually work?

---

<div class="post-metadata">

**Author:** ![lukeelmers](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukeelmers/32/35230_2.png) [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)\
**Post date:** [September 24, 2020, 8:53pm UTC](https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265/2 "2020-09-24T20:53:21Z")

</div>

I think the issue you are running into is that the `essql` function only accepts something of type `filter` as input (because it applies any global filters to the query), and the context is different by the time you get to the `lte` function (it's the number returned from `getCell "foo"`), so `essql` doesn't know what to do with it -- which explains the cryptic `Can not cast number to any of filter` error.

The reason most of our data fetching functions are set up this way is because they originally assumed that generally, fetching data was going to happen at the beginning of an expression instead of in the middle. Obviously this is an assumption we should revisit, because yours is a perfectly valid use case.

In the meantime, a workaround would be to use the `var` and `var_set` functions, which can be used to store globals that are accessible later in the expression. In your case, it would look something like this:

```auto
    filters group="default"
    | var_set name="index2Count" value={essql query="select count(*) from \"index2*\" " | head 1 | getCell "count"}
    | essql 
      query="select SUM(foo) as foo_total from \"index*\" "
    | head 1
    | if { context | head 1 | getCell "foo" | lte {var "index2Count"} } then={markdown "True" | render} else={markdown "False" | render}

```

---

<div class="post-metadata">

**Author:** ![lukeelmers](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukeelmers/32/35230_2.png) [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)\
**Post date:** [September 24, 2020, 8:55pm UTC](https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265/3 "2020-09-24T20:55:54Z")

</div>

Ah, I just re-read your post and realized you are on 7.4. Unfortunately, I can't think of a good workaround for this that would work in 7.4 ☹

`var` and `var_set` were [introduced in 7.7](https://github.com/elastic/kibana/pull/54788)

---

<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:** [October 22, 2020, 8:56pm UTC](https://discuss.elastic.co/t/errors-using-query-inside-sub-expression/249265/4 "2020-10-22T20:56:01Z")

</div>

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