# Named Query - SELECT COALESCE issue

**URL:** <https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808>\
**Category:** Ignition\
**Tags:** ignition79, database\
**Created:** [April 3, 2019, 12:13am UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808 "2019-04-03T00:13:59Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [April 3, 2019, 12:13am UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/1 "2019-04-03T00:13:59Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 2, 2019, 12:46pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/2 "2019-05-02T12:46:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [May 2, 2019, 1:07pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/3 "2019-05-02T13:07:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 2, 2019, 1:21pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/4 "2019-05-02T13:21:56Z")

</div>

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.

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

```

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [May 2, 2019, 1:31pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/5 "2019-05-02T13:31:33Z")

</div>

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 "/>".
```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [May 2, 2019, 2:41pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/6 "2019-05-02T14:41:52Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![paulm1](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paulm1/32/5900_2.png) [@paulm1](https://forum.inductiveautomation.com/u/paulm1)\
**Post date:** [May 2, 2019, 6:24pm UTC](https://forum.inductiveautomation.com/t/named-query-select-coalesce-issue/23808/7 "2019-05-02T18:24:38Z")

</div>

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.