Null Query Error - Binding needs "double"

Ignition
1

Hello,

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

The error I get is :

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 :

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

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.

2

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.

3

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.

4

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.

2 Likes
5

I tried this but I am getting an error.

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

6

Move the AS outside the COALESCE.

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

Thank you!

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