# Turning where clauses on or off

**URL:** <https://forum.inductiveautomation.com/t/turning-where-clauses-on-or-off/3143>\
**Category:** Ignition\
**Created:** [August 1, 2011, 5:10pm UTC](https://forum.inductiveautomation.com/t/turning-where-clauses-on-or-off/3143 "2011-08-01T17:10:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 1, 2011, 5:10pm UTC](https://forum.inductiveautomation.com/t/turning-where-clauses-on-or-off/3143/1 "2011-08-01T17:10:11Z")

</div>

Hey all,

I am making an alarm page on one of my screens where I can sort alarms by the alarm type, but I would also like to show all of the alarms for the given time range. I have a drop down box with the following SQL statement bound to it:

`SELECT DISTINCT STATE_NAME
FROM ALERT_LOG
where displaypath='Kill Floor Alarms'
union all
select 'All'
order by STATE_NAME asc`

So that it will pick all of the different alarms that are in the alarm database associated with “Kill Floor Alarms”, I also have it adding an “All” selection to the list.

Now, on my table I have this SQL statement:

`SELECT STATE_NAME, ACTIVE_TIMESTAMP, CLEARED_TIMESTAMP, DATEDIFF(minute,ACTIVE_TIMESTAMP,CLEARED_TIMESTAMP) as Downtime 
FROM ALERT_LOG
WHERE displaypath='Kill Floor Alarms'
	  and STATE_NAME='{Root Container.Dropdown 1.selectedStringValue}'
	  and ACTIVE_TIMESTAMP>='{Root Container.Date Range.startDate}'
	  and ACTIVE_TIMESTAMP<='{Root Container.Date Range.endDate}'
order by ACTIVE_TIMESTAMP desc`

But I can’t figure out how to display all of the alarms if “All” is selected in the dropdown menu. Is there a way I can enable/disable the and STATE\_NAME=’{Root Container.Dropdown 1.selectedStringValue}’ part of the where clause if “All” is selected? I’m not really an SQL guru so I don’t know if there is an easy way of doing this or not. I’ve tried putting SELECT “All” as Null in on the dropdown box but that didn’t work so now I’m not sure what to do. Any ideas would be much appreciated, thanks!

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [August 1, 2011, 7:33pm UTC](https://forum.inductiveautomation.com/t/turning-where-clauses-on-or-off/3143/2 "2011-08-01T19:33:13Z")

</div>

Just add a dynamic property to the dropdown component called whereClause that is a string. Bind the new property to the following expression:`if({Root Container.Dropdown 1.selectedStringValue} = 'All', "1=1", "STATE_NAME='" + {Root Container.Dropdown 1.selectedStringValue} + "'"`Just use the whereClause in the SQL query:`SELECT STATE_NAME, ACTIVE_TIMESTAMP, CLEARED_TIMESTAMP, DATEDIFF(minute,ACTIVE_TIMESTAMP,CLEARED_TIMESTAMP) as Downtime 
FROM ALERT_LOG
WHERE displaypath='Kill Floor Alarms'
     and {Root Container.Dropdown 1.whereClause} 
     and ACTIVE_TIMESTAMP>='{Root Container.Date Range.startDate}'
     and ACTIVE_TIMESTAMP<='{Root Container.Date Range.endDate}'
order by ACTIVE_TIMESTAMP desc`That way when All is selected the query goes AND 1=1 which brings back everything otherwise it filters it. Hope that helps.

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [August 1, 2011, 7:43pm UTC](https://forum.inductiveautomation.com/t/turning-where-clauses-on-or-off/3143/3 "2011-08-01T19:43:55Z")

</div>

Wow, that’s beautiful! I would have never thought of that, it worked great. Thanks!