# SQL Query Tag - I want null returned as 0

**URL:** <https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947>\
**Category:** Ignition\
**Created:** [April 22, 2015, 6:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947 "2015-04-22T18:37:04Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![tbackus](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tbackus/32/5091_2.png) [@tbackus](https://forum.inductiveautomation.com/u/tbackus)\
**Post date:** [April 22, 2015, 6:37pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/1 "2015-04-22T18:37:04Z")

</div>

So I have a SQL Query Tag that returns a Workorder. Later on in the process I use that Work Order number to query something else to give me production information. When the tag is [color=#FF0000]null[/color] and I try to use that value to lookup production information I get errors… But if I manually substitute a ZERO it works. Is it possible within the tag defenition somewhere to assign a default value? OR am I missing something in my SQL code that will do this?

`SELECT TOP 1 WorkOrderNumber
FROM MachineWorkOrders 
WHERE MachineID='{[.]Lane3}' 
ORDER BY StartDate 
DESC`

No matter what the great Google box gives me as possible answers I an unable to accomplish this…

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 22, 2015, 6:50pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/2 "2015-04-22T18:50:20Z")

</div>

Try COALESCE:

`SELECT TOP 1 COALESCE(WorkOrderNumber,0)
FROM MachineWorkOrders
WHERE MachineID='{[.]Lane3}'
ORDER BY StartDate
DESC`

---

<div class="post-metadata">

**Author:** ![tbackus](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tbackus/32/5091_2.png) [@tbackus](https://forum.inductiveautomation.com/u/tbackus)\
**Post date:** [April 22, 2015, 7:45pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/3 "2015-04-22T19:45:44Z")

</div>

Nope. Still the tag shows null. I have attached 2 screenshots… I have tried ISNULL () function also with the same result.  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c0905b7c01503e5abb6a1e0ed2c73bcab2f4812b.png)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/0527ad9fd31d418f7b8651048ac202718f1e00a6.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 22, 2015, 8:32pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/4 "2015-04-22T20:32:20Z")

</div>

Hmm… does the row for the work order exist?

Maybe something like this:

`SELECT CASE
         WHEN EXISTS(SELECT TOP 1 COALESCE(WorkOrderNumber,0)
					FROM MachineWorkOrders
					WHERE MachineID='{[.]Lane3}'
					ORDER BY StartDate DESC)
           THEN (SELECT TOP 1 COALESCE(WorkOrderNumber,0)
				FROM MachineWorkOrders
				WHERE MachineID='{[.]Lane3}'
				ORDER BY StartDate DESC)
         ELSE '0'
		END `

EDIT: Probably without the COALESCE… getting tired… 😒

---

<div class="post-metadata">

**Author:** ![tbackus](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tbackus/32/5091_2.png) [@tbackus](https://forum.inductiveautomation.com/u/tbackus)\
**Post date:** [April 22, 2015, 8:45pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/5 "2015-04-22T20:45:58Z")

</div>

No the row for the workorder does NOT exist… That is why I am trying to return a ZERO instead of nothing.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [April 22, 2015, 8:51pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/6 "2015-04-22T20:51:29Z")

</div>

Ah. Then that last one I posted should be closer.

Another option is to use an expression tag to coalesce the first one.

---

<div class="post-metadata">

**Author:** ![tbackus](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tbackus/32/5091_2.png) [@tbackus](https://forum.inductiveautomation.com/u/tbackus)\
**Post date:** [April 22, 2015, 8:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/7 "2015-04-22T20:58:37Z")

</div>

Now there’s a thought… hmmmm.

---

<div class="post-metadata">

**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\
**Post date:** [April 22, 2015, 9:08pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/8 "2015-04-22T21:08:03Z")

</div>

Since there is is no record returned with your WHERE clause, there is noting to coalesce. But you can put your Select statement inside the coalesce.  
Try - `SELECT COALESCE(
  (SELECT TOP 1 WorkOrderNumber
   FROM MachineWorkOrders
   WHERE MachineID='{[.]Lane3}'
   ORDER BY StartDate
   DESC), 
   "WooHoo It Works")`

---

<div class="post-metadata">

**Author:** ![yifeiliu08](https://avatars.discourse-cdn.com/v4/letter/y/bbe5ce/32.png) [@yifeiliu08](https://forum.inductiveautomation.com/u/yifeiliu08)\
**Post date:** [May 4, 2020, 7:28am UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/9 "2020-05-04T07:28:07Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/e4d5ff2695100b32391dd6fb6394fc5164c139ad.png)  
Your Suggestion is very useful, i success.

---

<div class="post-metadata">

**Author:** ![dcamp1](https://avatars.discourse-cdn.com/v4/letter/d/dc4da7/32.png) [@dcamp1](https://forum.inductiveautomation.com/u/dcamp1)\
**Post date:** [May 4, 2020, 1:44pm UTC](https://forum.inductiveautomation.com/t/sql-query-tag-i-want-null-returned-as-0/9947/10 "2020-05-04T13:44:22Z")

</div>

Here’s one of my Queries for “Divide By Error”. I am using MSSQL.  
You will probably just use the ISNULL function, something like this.  
ISNULL(Column, 0) The 0 will replace the NULL value.

SELECT Top 30 t\_stamp, DailyWater, Daily\_Inj\_Runtime, ISNULL(DailyWater / NULLIF(Daily\_inj\_Runtime,0),0)\*60.0 as PHFR from injection order by t\_stamp desc