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.
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.
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.