Named Query Error

I have this script:

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 =
INNER JOIN alarm_event_data v2 ON =
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: An error occurred during the current command (Done status 0). Error converting data type nvarchar to float.

Any ideas?

Yes, Wiki - how to post code on this forum.


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 we see that when used with multiple data types, COALESCE returns the highest precedence type. Checking the type precedence list, 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.

1 Like

Do you recommend a better way of doing this?

That agrees with @dkhayes117's Named Query - SELECT COALESCE issue @dkhayes117.

1 Like

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.

1 Like

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