# SQL Syntax error While using Dynamic Property and Expression

**URL:** <https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239>\
**Category:** Ignition\
**Created:** [April 3, 2012, 2:06pm UTC](https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239 "2012-04-03T14:06:51Z")\
**Posts on this page:** 4\
**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:** [April 3, 2012, 2:06pm UTC](https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239/1 "2012-04-03T14:06:51Z")

</div>

I can’t seem to find the error in this SQL where clause. I’m binding the Where Clause of the SQL query to a Dynamic Property of a table, so I can toggle between two different where clauses depending on wither a check box is checked.

SQL Query

`SELECT 
	run_id, start_time, end_time, 
	TIMESTAMPDIFF(HOUR, start_time, end_time) "Run Time (Hours)",
	TIMESTAMPDIFF(MINUTE, start_time, end_time) "Run Time (Minutes)"
FROM
	product_run
{Root Container.Table.WhereClause}
	`

Where Clause Expression

`if({Root Container.cbEndDateEnable.selected} = 1, concat("WHERE line_id = ", {Root Container.drpLineSel.selectedStringValue}, " AND start_time >= ", {Root Container.pcalStartDateSel.date}, " AND end_time <= ", {Root Container.pcalEndDateSel.date}),
		concat("WHERE line_id = ", {Root Container.drpLineSel.selectedStringValue}, " AND start_time >= ", {Root Container.pcalStartDateSel.date}))`

Error Message

[quote]Exception: Error running query:  
SQLQuery(query=SELECT  
run\_id, start\_time, end\_time,  
TIMESTAMPDIFF(HOUR, start\_time, end\_time) “Run Time (Hours)”,  
TIMESTAMPDIFF(MINUTE, start\_time, end\_time) “Run Time (Minutes)”  
FROM  
product\_run  
WHERE line\_id = PDM Line 1 AND start\_time \>= 2012-03-01 07:06:00  
, database=)@0ms  
On: History.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 ‘Line 1 AND start\_time \>= 2012-03-01 07:06:00’ at line 7  
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 ‘Line 1 AND start\_time \>= 2012-03-01 07:06:00’ at line 7

Ignition v7.3.4 (b599)  
Java: Sun Microsystems Inc. 1.6.0\_29  
[/quote]

---

<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:** [April 3, 2012, 3:27pm UTC](https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239/2 "2012-04-03T15:27:33Z")

</div>

You have to put single quotes around the dates in the where clause. Try this:`if({Root Container.cbEndDateEnable.selected} = 1, concat("WHERE line_id = ", {Root Container.drpLineSel.selectedStringValue}, " AND start_time >= '", {Root Container.pcalStartDateSel.date}, "' AND end_time <= '", {Root Container.pcalEndDateSel.date}, "'"),
      concat("WHERE line_id = ", {Root Container.drpLineSel.selectedStringValue}, " AND start_time >= '", {Root Container.pcalStartDateSel.date}, "'"))`

---

<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:** [April 3, 2012, 4:11pm UTC](https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239/3 "2012-04-03T16:11:34Z")

</div>

That didn’t work. I get the same error:

[quote]Exception: Error running query:  
SQLQuery(query=SELECT  
run\_id, start\_time, end\_time,  
TIMESTAMPDIFF(HOUR, start\_time, end\_time) “Run Time (Hours)”,  
TIMESTAMPDIFF(MINUTE, start\_time, end\_time) “Run Time (Minutes)”  
FROM  
product\_run  
WHERE line\_id = PDM Line 1 AND start\_time \>= ‘2012-03-12 07:06:00’ AND end\_time \<= ‘2012-03-17 07:06:00’

```
, database=)@0ms

```

On: History.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 'Line 1 AND start\_time \>= ‘2012-03-12 07:06:00’ AND end\_time \<= ‘2012-03-17 07:06’ at line 7  
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 'Line 1 AND start\_time \>= ‘2012-03-12 07:06:00’ AND end\_time \<= ‘2012-03-17 07:06’ at line 7

Ignition v7.3.4 (b599)  
Java: Sun Microsystems Inc. 1.6.0\_29  
[/quote]

---

<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:** [April 3, 2012, 4:16pm UTC](https://forum.inductiveautomation.com/t/sql-syntax-error-while-using-dynamic-property-and-expression/4239/4 "2012-04-03T16:16:38Z")

</div>

Sorry, I missed the single quotes around the line id as well:`if({Root Container.cbEndDateEnable.selected} = 1, concat("WHERE line_id = '", {Root Container.drpLineSel.selectedStringValue}, "' AND start_time >= '", {Root Container.pcalStartDateSel.date}, "' AND end_time <= '", {Root Container.pcalEndDateSel.date}, "'"),
      concat("WHERE line_id = '", {Root Container.drpLineSel.selectedStringValue}, "' AND start_time >= '", {Root Container.pcalStartDateSel.date}, "'"))`You have to put quotes around string and dates but not numbers.