SQL / Expression Code Help

Ignition
1

I have a table on that is using this SQL statement;

[code]SELECT
*
FROM
dwg_list

{Root Container.tblDwgList.whereClause}[/code]

Where Clause

if({Root Container.txtDwgID.text} = "", concat("WHERE cntlr_id =", {Root Container.drpCntlrName.selectedValue}), concat("WHERE dwg_id =", {Root Container.txtDwgID.text}))
Error

[code]Exception: Error running query:
SQLQuery(query=SELECT
*
FROM
dwg_list

WHERE dwg_id =40_1000-Layout1.pdf, database=)@5000ms
On: Main Window 1.Root Container.tblDwgList.data
caused by GatewayException: Unknown column ‘40_1000’ in ‘where clause’
caused by MySQLSyntaxErrorException: Unknown column ‘40_1000’ in ‘where clause’

Ignition v7.5.0 (b1079)
Java: Oracle Corporation 1.7.0_05
[/code]

When I type into the ‘Root Container.txtDwgID.text’ text box ‘40_1000-Layout1.pdf’ i get the above error.

But if I change the SQL statement to

[code]
SELECT
*
FROM
dwg_list

WHERE dwg_id =‘40_1000-Layout1.pdf’[/code]

The query works

2

To get the single quotes around your string, try:

if({Root Container.txtDwgID.text} = "", concat("WHERE cntlr_id ='", {Root Container.drpCntlrName.selectedValue}), concat("WHERE dwg_id ='", {Root Container.txtDwgID.text})) + "'"

Or:

if({Root Container.txtDwgID.text} = "", concat("WHERE cntlr_id ='", {Root Container.drpCntlrName.selectedValue},"'"), concat("WHERE dwg_id ='", {Root Container.txtDwgID.text}, "'"))

The first illustrates the + operator with strings. The second is a bit more clean, using 3 parameters with the concat function instead of 2.