Named Query - SELECT COALESCE issue

Ignition
,
1

For a SQL Query Binding I have used the following successfully.

SELECT COALESCE(Disch, 0)
FROM table
WHERE t_stamp BETWEEN startTime and EndTime

I cannot get this to work inside a Named Query.
What am I doing wrong?

2

Unless you posted the query wrong, the parameters need to be :startTime and :EndTime.
Put a colon in front of the parameter names

3

Your right I posted it wrong. Here is the actual namedQuery I would like to add the coalesce to.

SELECT eqName
FROM maint
WHERE id LIKE :SelectedCell
4

I wrote this in MySQL syntax so you may have to change it a bit for another db. Concatenate the wild card % with the parameter either in front, behind, or both depending on your needs.

SELECT coalesce(eqName,0)
FROM maint
WHERE id like CONCAT('%', :SelectedCell, '%')
5

Here is what I just attempted -

SELECT coalesce(eqName, "No Selection")
FROM maint
WHERE id like CONCAT('%', :SelectedCell)

My db is MySQL. I received an error “Unable to Read response from Gateway”

GatewayException: Unable to read response from Gateway.
caused by org.xml.sax.SAXParseException; lineNumber: 4; columnNumber: 33; Element type "column" 
must be followed by either attribute specifications, ">" or "/>".
6

Okay, coalesce is to select the first non-null, and if your query returns a null value, it will replace it with ‘No Selection’. Coalesce does nothing for no rows returned, which is possible with the where clause. If you make your named query a scalar query, you can supply a fallback value that will be selected if no rows are returned. The error you got seems to be something to do with the parameter you are feeding the query. Does it have ‘>’ in the parameter?

7

Oh yeah, you’re right. That’s what’s happening. It’s when there is no rows returned.
As for the error, it didn’t make sense to me either. I don’t even have a line 4 in the query.
The script I attached earlier is the entire Named query. The parameter is the cell or row the operator selects from a table. I will look closer at the parameter and see if the error applies to it.

I will try the Scalar query and post back my results.
Thank you for sticking with me on this.

1 Like