# Named Query Error

**URL:** <https://forum.inductiveautomation.com/t/named-query-error/83856>\
**Category:** Ignition\
**Tags:** perspective, namedqueries\
**Created:** [January 5, 2024, 6:57pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856 "2024-01-05T18:57:42Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![scadafan](https://avatars.discourse-cdn.com/v4/letter/s/90db22/32.png) [@scadafan](https://forum.inductiveautomation.com/u/scadafan)\
**Post date:** [January 5, 2024, 6:57pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/1 "2024-01-05T18:57:42Z")

</div>

I have this script:

```auto
SELECT e1.eventtime AS 'Active Time', e2.eventtime AS 'Cleared Time', e1.source AS 'Name/Label', COALESCE (v1.intvalue, v1.floatvalue, v1.strValue) AS 'Out Of Spec Value'
FROM alarm_events e1
INNER JOIN alarm_events e2 ON e1.eventid = e2.eventid AND e1.eventtype = 0 AND e2.eventtype = 1
INNER JOIN alarm_event_data v1 ON e1.id = v1.id
INNER JOIN alarm_event_data v2 ON e2.id = v2.id
WHERE e1.eventtime BETWEEN '2024-01-04 06:00:00' AND '2024-01-05 08:00:00'

```

It works perfectly in the database query browser. However, when I use it in Named Queries I get this error:

`com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred during the current command (Done status 0). Error converting data type nvarchar to float.`

Any ideas?

---

<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:** [January 5, 2024, 6:59pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/2 "2024-01-05T18:59:35Z")

</div>

> [@scadafan](#):
>
> Any ideas?

Yes, [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831).

---

<div class="post-metadata">

**Author:** ![scadafan](https://avatars.discourse-cdn.com/v4/letter/s/90db22/32.png) [@scadafan](https://forum.inductiveautomation.com/u/scadafan)\
**Post date:** [January 5, 2024, 7:01pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/3 "2024-01-05T19:01:25Z")

</div>

Fixed.

---

<div class="post-metadata">

**Author:** ![Felipe\_CRM](https://avatars.discourse-cdn.com/v4/letter/f/dfb087/32.png) [@Felipe\_CRM](https://forum.inductiveautomation.com/u/Felipe_CRM)\
**Post date:** [January 5, 2024, 7:18pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/4 "2024-01-05T19:18:53Z")

</div>

The error message suggests that the issue is with your COALESCE as that is the only place where the query might try to coerce a `nvarchar` to `float`.

Looking at the documentation for [COALESCE](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16) we see that when used with multiple data types, COALESCE returns the highest precedence type. Checking the [type precedence list](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16), `float` has the highest precedence of the types you are using here so everything gets coerced to `float`.

Be careful when using COALESCE with multiple data types. You'd have to do some explicit conversion to `nvarchar` instead.

---

<div class="post-metadata">

**Author:** ![scadafan](https://avatars.discourse-cdn.com/v4/letter/s/90db22/32.png) [@scadafan](https://forum.inductiveautomation.com/u/scadafan)\
**Post date:** [January 5, 2024, 7:19pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/5 "2024-01-05T19:19:43Z")

</div>

Do you recommend a better way of doing this?

---

<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:** [January 5, 2024, 7:23pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/6 "2024-01-05T19:23:03Z")

</div>

That agrees with @dkhayes117's [Named Query - SELECT COALESCE issue](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808) @dkhayes117.

---

<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:** [January 5, 2024, 7:27pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/7 "2024-01-05T19:27:10Z")

</div>

> [@scadafan](#):
>
> a better way

Try using SQL's `CASE` construct with `someValue IS NULL` conditions. But be aware that the final column type must be known. Consider casting everything to string.

---

<div class="post-metadata">

**Author:** ![scadafan](https://avatars.discourse-cdn.com/v4/letter/s/90db22/32.png) [@scadafan](https://forum.inductiveautomation.com/u/scadafan)\
**Post date:** [January 7, 2024, 11:56pm UTC](https://forum.inductiveautomation.com/t/named-query-error/83856/8 "2024-01-07T23:56:24Z")

</div>

Thank you. Yes that was the issue. I just removed the strvalue and it worked great after that. I don't need it anyways. Thank you very much