Filter a table sql query

Ignition
1

Hi, I trying to modify a sql query I found in the “ignition demo project” I have a table with this code:

[code]SELECT * FROM inventory

{Root Container.option.WhereClause}
[/code]

I have the where clause in a custom property in a container named option, the expression is this:

[code]“WHERE lastUpdated >= '” + {Root Container.option.Popup Calendar.date} +
“’ AND lastUpdated <= '”+ {Root Container.option.Popup Calendar 1.date} + "’ "+

if(len({Root Container.option.Search Field.text}) > 0,
“AND (name LIKE '%” + {Root Container.option.Search Field.text}
+"%’ OR description LIKE ‘%" + {Root Container.option.Search Field.text}
+"%’) ",
“”) +

if({Root Container.option.Dropdown.selectedLabel} != “Any”,
“AND location=’” + {Root Container.option.Dropdown.selectedLabel} + “’ “,””)[/code]

this code works and filters a table if there is a date selected or if there is something in the Search field, but [color=#FF0000]I want to remove de date condition[/color] but I getting errors, I have this when I remove the date condition:

[code]"WHERE "+

if(len({Root Container.option.Search Field.text}) > 0,
“(name LIKE '%” + {Root Container.option.Search Field.text}
+"%’ OR description LIKE ‘%" + {Root Container.option.Search Field.text}
+"%’) ",
“”) +

if({Root Container.option.Dropdown.selectedLabel} != “Any”,
“AND location=’” + {Root Container.option.Dropdown.selectedLabel} + “’ “,””)[/code]

I think there is some quotes “” I need to clear but I can’t find the problem, please help me.

2

You’ll get a syntax error like that at AND. Try this instead:

#Add (1=1)
if(len({Root Container.option.Search Field.text}) > 0,
  "(name LIKE '%" + {Root Container.option.Search Field.text}
  +"%' OR description LIKE '%" + {Root Container.option.Search Field.text}
  +"%') ",
  "(1=1)") +
3

Thanks man! you save me again, but I don’t understand what you mean with (1=1), it worked but i don’t know what is (1=1) doing.

4

“(1=1)” always returns a truth value of TRUE. Your code was always sending the WHERE keyword so you needed to make sure all possible outcomes had valid SQL syntax. Ignoring the second condition for a second, the two possible outcomes of your code returned either:

(a) WHERE FirstTrueOrFalseCondition

or:

(b) WHERE

This second syntax at (b) is invalid without something to follow it.

When adding the second condition, you have a total of four (2 x 2) possible outcomes:

© WHERE FirstTrueOrFalseCondition AND SecondTrueOrFalseCondition

(d) WHERE FirstTrueOrFalseCondition

(e) WHERE AND SecondTrueOrFalseCondition

(f) WHERE

The last two statements, (e) and (f), are incorrect here, which can directly be attributed to the fact that (b) was wrong. By adding the code that I did at (b) it resolved the problems with these last two statements.

5

Thanks again for the answer :thumb_right: