Database Query to return 0 when no rows

Archived Topics
1

This is what i have now but when there are no rows i recieve an error. i would like to set it to 0 when no rows are present.

SELECT DISTINCT IF (ack_time IS NULL, 2, IF( ack_time IS NOT NULL AND clear_time IS NULL,1, 0)) AS statetemp
FROM alarmlog WHERE item_name=’{Root Container.DiversionResevoirFailtoOpen.item_name}’ ORDER BY statetemp DESC LIMIT 1

2

Hi-

The quickest thing is probably to just wrap your whole expression in another IF:

SELECT DISTINCT IF(count(*)>0, IF (ack_time IS NULL, 2, IF( ack_time IS NOT NULL AND clear_time IS NULL,1, 0)), 0) AS statetemp FROM alarmlog WHERE item_name='{Root Container.DiversionResevoirFailtoOpen.item_name}' ORDER BY statetemp DESC LIMIT 1

You’re checking on the condition “count(*)>0”, that is, “are there any rows”, as influenced by your where clause.

On a side note, I just want to point out that the combination of DISTINCT, LIMIT and specifying a specific item_name in the where clause (I’m assuming you don’t have 2 items with the same name) are a bit redundant. This query would work, but the extra parameters might be a little unclear to someone else looking at it. Simply doing the following would suffice:

SELECT IF(count(*)>0, IF (ack_time IS NULL, 2, IF( ack_time IS NOT NULL AND clear_time IS NULL,1, 0)), 0) AS statetemp FROM alarmlog WHERE item_name='{Root Container.DiversionResevoirFailtoOpen.item_name}'

Hope that helps,

3

There is a “Fallback value” in FactoryPMI that you can set to 0 in this case. You can’t use it for the “Query Browser”, but in the “SQL Binding” the value exists. Simply check Use Fallback Value and type a value of 0 in the field.

As Colby pointed out, the DISTINCT keyword is redundant. The ORDER BY statetemp is important in this case because that query is looking at ALL alarms in the alarm history table. There could be an alarm that’s unacknowledged and one that’s acknowledged and not cleared. The LIMIT 1 is a good idea since you’re binding what could possibly be a list of values to a single property. It’s not necessary since FactoryPMI will select the first row, but it’s a good idea.