# SQL Table Filtering With IF Statement

**URL:** <https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115>\
**Category:** Ignition\
**Created:** [March 7, 2012, 7:37pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115 "2012-03-07T19:37:17Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [March 7, 2012, 7:37pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115/1 "2012-03-07T19:37:17Z")

</div>

I’m trying to create some filtering options for the user to allow them to filter the data the see in the table. Here is what I have:

`WHERE	
	projects.state = {Root Container.drpStateSel.selectedValue}
	AND {Root Container.cbStateFilter.selected} = 1`

This will apply the filter when the dbStateFilter check box is checked.  
What i need help with is, I want to return all the records when the filter is turned off and order by the latest record which I can use projects.id ASC for that. But what i really need is an IF( statement in the SQL query but I can’t seem to get that to work. I tried this:

`IF({Root Container.rb_RemoveFilter.selected} = 0, WHERE	
	projects.state = {Root Container.drpStateSel.selectedValue}
	AND {Root Container.cbStateFilter.selected} = 1, ORDER BY projects.id)`  
But I get this Error

Exception: Error running query:  
SQLQuery(query=SELECT  
\*  
FROM  
projects  
JOIN  
customers  
on (projects.cust=customers.id)  
JOIN  
proj\_state  
on (projects.state=proj\_state.id)  
IF(1 = 0, WHERE   
projects.state = 1  
AND 0 = 1, ORDER BY projects.id)

, database=)@0ms  
On: Overview.Root Container.Table.data  
caused by GatewayException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF(1 = 0, WHERE   
projects.state = 1  
AND 0 = 1, ORDER BY projects.id)’ at line 11  
caused by MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF(1 = 0, WHERE   
projects.state = 1  
AND 0 = 1, ORDER BY projects.id)’ at line 11

Ignition v7.3.3 (b570)  
Java: Oracle Corporation 1.7.0\_02

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [March 7, 2012, 10:54pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115/2 "2012-03-07T22:54:05Z")

</div>

Hi,

Yes, you’re mixing the expression language syntax in with SQL, which isn’t valid. Instead, I would do the following: create a dynamic property on the table which is a string value, and builds up the WHERE clause. That way, you can use expression in the dynamic property, and then your Query binding just includes the result.

For example, imagine you added a dynamic property called “WhereClause”, which was bound to an expression:

```auto
if({Root Container.rb_RemoveFilter.selected} = 0, concat("WHERE projects.state = ",{Root Container.drpStateSel.selectedValue}), "ORDER BY projects.id")
```

Then in your query:

```auto
select * from table {Root Container.yourTableName.WhereClause}
```

Hope this helps,

---

<div class="post-metadata">

**Author:** ![jlr573](https://avatars.discourse-cdn.com/v4/letter/j/c6cbf5/32.png) [@jlr573](https://forum.inductiveautomation.com/u/jlr573)\
**Post date:** [March 9, 2012, 1:45pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115/3 "2012-03-09T13:45:36Z")

</div>

Thanks Colby works like a charm

---

<div class="post-metadata">

**Author:** ![MiddleC](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MiddleC](https://forum.inductiveautomation.com/u/MiddleC)\
**Post date:** [March 12, 2012, 6:37pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115/4 "2012-03-12T18:37:52Z")

</div>

I’m attempting a similar filtering method using calendar dates and table time stamps. When using the concat() to build a Where statement I recieve syntax errors.

I can’t get the code below to work.

`SELECT ISNULL(SUM(Feet),0)
From Yield
{Root Container.Numeric Label.Day_Where}`

`concat("WHERE CONVERT(VARCHAR(11),t_stamp)=",{Root Container.Calendar.CalDay})
`

The following works but doesn’t use the concat function (i.e. I guess it’s sorta hard coded). I need to construct Where code dynamically.

`SELECT ISNULL(SUM(Feet),0)
From Yield
WHERE (CONVERT(VARCHAR(11),t_stamp)=CONVERT(VARCHAR(11),'{Root Container.Label 1.text}'))AND Shift = 1`

---

<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:** [March 12, 2012, 6:50pm UTC](https://forum.inductiveautomation.com/t/sql-table-filtering-with-if-statement/4115/5 "2012-03-12T18:50:57Z")

</div>

Don’t forget to concatenate your single quotes into your expression. 😉