# Null Query Error - Binding needs "double"

**URL:** <https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [December 29, 2022, 9:15pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079 "2022-12-29T21:15:21Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [December 29, 2022, 9:15pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/1 "2022-12-29T21:15:21Z")

</div>

Hello,

I have a numeric display, and named query binding on the value.

The error I get is :

```auto
Exception: Conversion error: query returned null, binding needs "double"
on query binding for "Main Menu.Root Container.Numeric Label.value"

Ignition v8.1.10 (b2021090812)
Java: Azul Systems, Inc. 11.0.11

```

and the query is :

```auto
SELECT 
  Sum(loads.Size) AS Yards
FROM loads
Where
 StartTime BETWEEN :Start_Date AND :End_Date

```

I tested it in the query browser and got a successful result without the

```auto
Where
 StartTime BETWEEN :Start_Date AND :End_Date

```

But that doesn't sound relevant to the error. So i'm not sure what is going on.

Thank you in advance.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [December 29, 2022, 9:21pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/2 "2022-12-29T21:21:55Z")

</div>

I'm not sure what the problem is with the information provided but did you set the named query _Query Type_ to **Scalar Query**? That will return a single value rather than a dataset.

You can demonstrate the difference by running a simple query such as  
`SELECT 17 AS myData`  
in the named query editor and test it with Query Type set to Query and then Scalar Query.

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [December 29, 2022, 9:42pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/3 "2022-12-29T21:42:26Z")

</div>

> [@Transistor](#):
>
> set the named query _Query Type_ to **Scalar Query**?

One of the first things I checked, and it is set to Scalar Query. I tried with query and still no luck.

The problem is the numeric display is red and not showing correct value.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [December 29, 2022, 10:12pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/4 "2022-12-29T22:12:31Z")

</div>

You are using an aggregate function. Aggregate functions will return a row even if there are no rows matching the where clause. You can't use a Scalar Query mode to provide a fallback because that only kicks in when there's no rows returned.

I recommend adding an outer query to the named query that uses coalesce to provide a zero.

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [December 29, 2022, 11:11pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/5 "2022-12-29T23:11:10Z")

</div>

> [@pturmel](#):
>
> I recommend adding an outer query to the named query that uses coalesce to provide a zero.

I tried this but I am getting an error.

```auto
SELECT 
  COALESCE(Sum(loads.Size) AS Yards, 0)
FROM loads
Where
 StartTime BETWEEN :Start_Date AND :End_Date

```

the error seems to be syntax related - AS yards, 0- says to check mysql server version

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [December 29, 2022, 11:30pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/6 "2022-12-29T23:30:45Z")

</div>

Move the `AS` outside the COALESCE.

```auto
SELECT COALESCE(Sum(loads.Size), 0) AS Yards
FROM loads
WHERE StartTime BETWEEN :Start_Date AND :End_Date

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [December 29, 2022, 11:40pm UTC](https://forum.inductiveautomation.com/t/null-query-error-binding-needs-double/68079/7 "2022-12-29T23:40:59Z")

</div>

Thank you!

been getting rusty. but gonna hop back into it with this new job.