SQL Query Tag - I want null returned as 0

Ignition
1

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…

2

Try COALESCE:

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

3

Nope. Still the tag shows null. I have attached 2 screenshots… I have tried ISNULL () function also with the same result.


Screenshot1.png796×599 16.3 KB

4

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… :unamused:

5

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

6

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

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

7

Now there’s a thought… hmmmm.

8

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")

1 Like
9

image
Your Suggestion is very useful, i success.

10

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