# SQL error on template dataset using custom property in the where clause **URL:** \ **Category:** Ignition\ **Tags:** vision\ **Created:** [October 8, 2025, 3:22pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287 "2025-10-08T15:22:39Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![compuzak](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@compuzak](https://forum.inductiveautomation.com/u/compuzak)\ **Post date:** [October 8, 2025, 3:22pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/1 "2025-10-08T15:22:39Z")
I get the following error in the dataset when I open the template in designer mode or the form using the template is opened in user mode. The dataset uses a custom property in the where clause for ScaleNo. The custom property isn’t populated before the dataset query is run and that causes the SQL syntax error. `Error running query:` `SQLQuery(query=SELECT RunNo` `, VarietyName` `, LEFT(BlockID, 4) AS LotNo` `, BlockName AS LotName` `, GradeName` `, SizeName` `, ProductType` `FROM TrnQcifyScaleConfig` `WHERE PacklineName = ''` **`AND ScaleNo = AND`** `ActiveConfigYN = 'Y', database=)@30000ms` `On: tQcifyData.tQcifyData.dsCurrLot` Here is the dataset SQL query: `SELECT RunNo` `, VarietyName` `, LEFT(BlockID, 4) AS LotNo` `, BlockName AS LotName` `, GradeName` `, SizeName` `, ProductType` `FROM TrnQcifyScaleConfig` `WHERE PacklineName = '{tQcifyData.PacklineName1}'` **`AND ScaleNo = {tQcifyData.ScaleNo1} AND`** `ActiveConfigYN = 'Y'` Is there any way to delay updating this query until the integer custom property ScaleNo1 is populated? Or is there some syntax in the SQL that would convert the blank value to zero (ISNULL still throws an error due to invalid syntax)? Or anything else to prevent this error when the window is opened? It causes the screen to turn pink when the window is opened, it quickly clears, but makes the user feel like there is something wrong. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 8, 2025, 3:38pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/2 "2025-10-08T15:38:09Z")
Tip: rather than format each line of code individually, select the whole block and use the \ button. It gives syntax highlighting too. Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 8, 2025, 3:44pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/3 "2025-10-08T15:44:33Z")
> [@compuzak](#): > > AND ScaleNo = {tQcifyData.ScaleNo1} Try, ` AND ScaleNo = COALESCE({tQcifyData.ScaleNo1}, 0)` > **[W3Schools.com](https://www.w3schools.com/sql/func_sqlserver_coalesce.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. On a separate note, you're passing parameters as QueryStrings. These are vulnerable to SQL injection. Use Value instead. Your query then becomes ```python SELECT RunNo , VarietyName , LEFT(BlockID, 4) AS LotNo , BlockName AS LotName , GradeName , SizeName , ProductType FROM TrnQcifyScaleConfig WHERE PacklineName = :tQcifyData.PacklineName1 AND ScaleNo = COALESCE(:tQcifyData.ScaleNo1, 0) AND ActiveConfigYN = 'Y' ``` I'd say the ActiveConfigYN column should be a boolean, not a string. ---
**Author:** ![Clancy\_Cavanaugh](https://avatars.discourse-cdn.com/v4/letter/c/45deac/32.png) [@Clancy\_Cavanaugh](https://forum.inductiveautomation.com/u/Clancy_Cavanaugh)\ **Post date:** [October 8, 2025, 4:22pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/4 "2025-10-08T16:22:57Z")
Pretty sure you want to remove the apostrophes around :tQcifyData.PacklineName1 It should automatically handle that as long as the parameter has the string data type. ```sql SELECT RunNo , VarietyName , LEFT(BlockID, 4) AS LotNo , BlockName AS LotName , GradeName , SizeName , ProductType FROM TrnQcifyScaleConfig WHERE PacklineName = :tQcifyData.PacklineName1 AND ScaleNo = COALESCE(:tQcifyData.ScaleNo1, 0) AND ActiveConfigYN = 'Y' ``` ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 8, 2025, 4:41pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/5 "2025-10-08T16:41:37Z")
> [@Clancy\_Cavanaugh](#): > > remove the apostrophes around :tQcifyData.PacklineName1 Oops! I noticed that and forgot to remove them. Fixed! Thanks. ---
**Author:** ![compuzak](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@compuzak](https://forum.inductiveautomation.com/u/compuzak)\ **Post date:** [October 8, 2025, 4:49pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/6 "2025-10-08T16:49:57Z")
The dataset custom property is not using a named query (maybe that is the solution). It is just using the SQL statements in the dataset property ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/b/9b79a891658ead599bcb35dcc827ea4be014a4cf.png) The problem is not with the alpha field, it is with the ScaleNo1 custom property field. It evaluates to blank and even with COALESCE (like with ISNULL) it gives a syntax error. `SQLQuery(query=SELECT RunNo` `, VarietyName` `, LEFT(BlockID, 4) AS LotNo` `, BlockName AS LotName` `, GradeName` `, SizeName` `, ProductType` `FROM TrnQcifyScaleConfig` `WHERE PacklineName = ''` `**AND ScaleNo = COALESCE(, 0) AND`\*\* `ActiveConfigYN = 'Y', database=)@30000ms` `On: tQcifyData.tQcifyData.dsCurrLot` ---
**Author:** ![Clancy\_Cavanaugh](https://avatars.discourse-cdn.com/v4/letter/c/45deac/32.png) [@Clancy\_Cavanaugh](https://forum.inductiveautomation.com/u/Clancy_Cavanaugh)\ **Post date:** [October 8, 2025, 4:55pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/7 "2025-10-08T16:55:12Z")
Is your ScaleNo1 property a string not an integer? ---
**Author:** ![compuzak](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@compuzak](https://forum.inductiveautomation.com/u/compuzak)\ **Post date:** [October 8, 2025, 4:58pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/8 "2025-10-08T16:58:41Z")
ScaleNo1 is integer. It works without the error using the named query and passing the properties to the named query. Thanks for steering me in the right direction, this issue has been resolved. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 8, 2025, 10:21pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/9 "2025-10-08T22:21:58Z")
Please see [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). ---
**Author:** ![compuzak](https://avatars.discourse-cdn.com/v4/letter/c/71c47a/32.png) [@compuzak](https://forum.inductiveautomation.com/u/compuzak)\ **Post date:** [October 8, 2025, 10:37pm UTC](https://forum.inductiveautomation.com/t/sql-error-on-template-dataset-using-custom-property-in-the-where-clause/111287/10 "2025-10-08T22:37:01Z")
```python SELECT RunNo , VarietyName , LEFT(BlockID, 4) AS LotNo , BlockName AS LotName , GradeName , SizeName , ProductType FROM TrnQcifyScaleConfig WHERE PacklineName = :PacklineName AND ScaleNo = :ScaleNo AND ActiveConfigYN = 'Y' ``` Thank you, I appreciate the help.