SQL Syntax error While using Dynamic Property and Expression

Ignition
1

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]

2

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}, "'"))

3

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]

4

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.