# SQL Tag Fallback Value

**URL:** <https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115>\
**Category:** Archived Topics\
**Created:** [April 28, 2009, 1:39pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115 "2009-04-28T13:39:41Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 28, 2009, 1:39pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/1 "2009-04-28T13:39:41Z")

</div>

Is there a way to define a fallback value for a client tag?

Previously I had a dynamic property bound to a query with a fallback value. I then used this property in other bound queries. This all worked great since I had the fallback value.

I recently changed these dynamic properties to bind them to SQL tags. The problem is when the tag does not return a value. I know I can use the if() function in a dynamic property but this would not be necessary with a fallback value.

So… unless you have another trick for me, the options as I see it are:

1. SQL Tag fallback, How? (Feature request?)
2. Use an if() function in a dynamic property referencing the SQLTag
3. Go back to having the query in the dynamic property.

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 28, 2009, 4:50pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/2 "2009-04-28T16:50:06Z")

</div>

I don't understand, what does this mean:

> [@](#):
>
> The problem is when the tag does not return a value

Tags do not "return a value". They are not functions. They always have a value (that value might be null, but it is still a value)

If protecting against null is what you're talking about, I recommend the coalesce expression function

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 28, 2009, 6:58pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/3 "2009-04-28T18:58:15Z")

</div>

Ok, I'll bite...

from wikipedia.

> [@](#):
>
> Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database.

Hence, the query (which the tag is bound to) does not return a value but a 'null'. So by definition a null is an absence of value. But I do get your point, an ascii value of 0 is still a value.

Thanks for the tip on coalesce().  
Since I am trying to do this at the SQLTag level, I have this as my query. I will let this run and see what happens.

```auto
SELECT COALESCE(OrderNumber,99999) FROM L8CurrentOrder
```

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 28, 2009, 8:24pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/4 "2009-04-28T20:24:12Z")

</div>

You are looking at too precise a definiton of null - see the full page of:  
[en.wikipedia.org/wiki/Null\_(computer\_programming](http://en.wikipedia.org/wiki/Null_(computer_programming))

and you’ll see that the term null can have many meanings. In our case (which is the FactoryPMI environment, not a SQL database), null is a value. It is a special value that may mean “no valid value” (depending on context - designers can use the special null value to mean whatever they please).

The fallback feature that you’re talking about doesn’t come into play when null is returned, it comes into play when nothing at all is returned. This means a SQL query that returns zero rows. This is different than a query returning one or more rows that may have null values in them.

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 28, 2009, 9:38pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/5 "2009-04-28T21:38:48Z")

</div>

Thanks for the lesson Carl, ignorance is a hard thing to overcome!

I was basing this post on the definition of null being an absence of value. Looking at this problem again after reading your post it is clear that I do not know what is happening yet. I did not differentiate between a null value and no records returned.

Here is the gateway error that ocurred after switching to an SQLTag. Notice the WHERE clause, OrderNumber = .  
Since I do not ‘see’ a value in this message I made an assumption it was null.

[quote]2009-04-28 1:58:35AM ERROR ERROR(301): SQL error for “SELECT RollSequence FROM L8MachineInspection WHERE OrderNumber = AND OrderLineNumber = 1 AND t\_stamp = ‘2008-08-12 16:20:15’”: Incorrect syntax near the keyword ‘AND’. [/gateway]  
java.sql.SQLException: Incorrect syntax near the keyword ‘AND’.  
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)  
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)  
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)  
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)  
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)  
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1258)  
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)  
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway$2.run(Gateway.java:1940)  
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doDBAction(Gateway.java:2364)  
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.runQuery(Gateway.java:1925)  
at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doPost(Gateway.java:404)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)  
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)  
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)  
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)  
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)  
at com.inductiveautomation.factorypmi.gateway.ErrorReportValve.invoke(ErrorReportValve.java:95)  
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)  
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)  
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)  
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)  
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)  
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)  
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)  
at java.lang.Thread.run(Unknown Source)  
[/quote]

Let’s look again at the new SQL binding of the tag.

```auto
SELECT COALESCE(OrderNumber,99999) FROM L8CurrentOrder
```

Typically there is only one row in ‘L8CurrentOrder’ and it is written to by another program. I do not know if it just overwrites the data or if it deletes the row and writes a new one. I did verified that I am getting errors when the other program is updating the table.

Based on this new information, will the coalesce function work for both null and zero records?

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 28, 2009, 10:47pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/6 "2009-04-28T22:47:03Z")

</div>

No, coalesce will only work when there is a null value. If there are no rows in L8CurrentOrder, then the SELECT COALESCE… will also return no rows.

For cases where there are no rows, you can use the fallback value feature. I feel like we’re going in circles now though… ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:")

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 29, 2009, 1:23pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/7 "2009-04-29T13:23:53Z")

</div>

So do I.

> [@](#):
>
> I feel like we're going in circles now though... ![:scratch:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/76d1620bec252bfac07050c889ff5a91dc8a9c25.gif ":scratch:")

Please read and answer the first question in the first post.

> [@](#):
>
> Is there a way to define a fallback value for a client tag?

Thanks,

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [April 29, 2009, 2:39pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/8 "2009-04-29T14:39:49Z")

</div>

I was testing the tag’s query and found out that:

if the record ‘has no valid value’ then tag contains a null  
if no rows exist then tag contains a null

Therefore I played around with solving this in the query and came up with this.

`Declare @count as int;
set @count = (select count(*) from L8CurrentOrder);
if @count > 0
	select COALESCE(OrderNumber,99999)
	FROM [L8CurrentOrder]
else
	SELECT 99999 AS OrderNumber`

I looked and still could not find a fallback value option. It would be a nice feature.

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 29, 2009, 3:44pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/9 "2009-04-29T15:44:24Z")

</div>

Yeah, you’re right, the SQL binding on tags should definitely have a fallback value. All this time I thought you were using a tag value in a standard SQL query property binding. Sorry about the confusion.

---

<div class="post-metadata">

**Author:** ![TimE](https://avatars.discourse-cdn.com/v4/letter/t/ee59a6/32.png) [@TimE](https://forum.inductiveautomation.com/u/TimE)\
**Post date:** [May 17, 2013, 9:10pm UTC](https://forum.inductiveautomation.com/t/sql-tag-fallback-value/1115/10 "2013-05-17T21:10:58Z")

</div>

Do you know when the “fallback value” feature will be added for SQL tags?

I just added a SQL query tag with a write back value to an OPC item. What happens to the OPC write if there are no rows returned?

Thanks