SQL Table Filtering With IF Statement

Ignition
1

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

2

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:

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

Then in your query:

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

Hope this helps,

1 Like
3

Thanks Colby works like a charm

4

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

5

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