SQL Query error handler?

Ignition
1

Hi, I have the following SQL Query binding on a Numeric Label custom property:

==========
SELECT MAX(charge_no)
FROM zb_operating_record
WHERE line = {Root Container.LineName.selectedValue}

It works great except when the {Root Container.LineName.selectedValue} does not exist in the database. Any ideas on how I can check for this first in a SQL Query? I tried the EXISTS SQL conditiond but to no avail.

Thanks in advance.

2

Ignition offers the ability to have a fallback value, itā€™s probably just what you need

3 Likes
3

Thanks, I do have a Fallback Value but the label component still turns red when the query produces a NULL return. I think the SQL EXISTS condition should work but does not seem to in the Ignition SQL Query binding perhaps? Iā€™m going to try running the query in script and see if this helps. Thanks for the response.

4

Iā€™ve also had issues with the SQL EXISTS in the past. The syntax is a bit difficult as you want to create data from no data (while typical SQL is always about filtering and combining existing data). Be certain to try those queries in a regular SQL app (MySQL workbench, SQL Server management studio, ā€¦).

Can you show how your current query looks like (the full query+settings screen), and also tell us what dialect of SQL you are using?

1 Like
5

Look into COALESCE. I use that when i know there is a possibility my query will be returning NULL, and force the value to 0 for numerical results.

Seems Ignitions SQL ā€œFallback Valueā€ doesnā€™t quite cover what i thought it did.

EXISTS definitely works with Ignition, I have also been using that. But the syntax can be a bit odd for it. I think you wrap the whole SELECT statement in EXISTS.

SELECT EXISTS (
   SELECT MAX(charge_no)
   FROM zb_operating_record
   WHERE line = {Root Container.LineName.selectedValue}
)
6

The ā€˜issueā€™ is that the Fallback value only applies if the query itself breaks, eg an invalid table is queried, or the DB connection is no longer available. If the query entered is perfectly valid (and just happens to not return any rows) then thereā€™s really nothing we can catch.

COALESCE is a great suggestion, and probably the most broadly valid SQL. Thereā€™s also ā€˜regionalā€™ variants like IFNULL, ISNULL, NVL, etc.

2 Likes
7

@PGriffith, but when the gui component expects exactly one value, returning no rows isnā€™t valid.

Youā€™re right that, when defining such a query on a table, it will be a valid query and the result will be a table without rows. But when defining it on a component that requires a single value (like a text or numeric field), the gui will show the query isnā€™t valid.

This case is perfect to catch with the fallback value in Ignition. See the screenshots (the Trucks table in the DB exists, thereā€™s just no truck with id 9999).

invalidQueryNoFallback.png477Ɨ510 33.4 KB

invalidQueryFallback.png479Ɨ507 35.4 KB

However, the problem here is that the MAX function returns exactly one value, but the returned value is NULL, which doesnā€™t get converted correctly to a number (converting NULL to a text field is no problem though, it just results in empty string). So in this case, you indeed need to check for the value being NULL, either with COALESCE or with an IF-statement.

I didnā€™t notice the MAX function in the original query, which is why I suggested the fallback check.