# SQL Query error handler? **URL:** \ **Category:** Ignition\ **Created:** [June 26, 2017, 1:35am UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623 "2017-06-26T01:35:13Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![qversa](https://avatars.discourse-cdn.com/v4/letter/q/35a633/32.png) [@qversa](https://forum.inductiveautomation.com/u/qversa)\ **Post date:** [June 26, 2017, 1:35am UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/1 "2017-06-26T01:35:13Z")
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. ---
**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\ **Post date:** [June 26, 2017, 11:59am UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/2 "2017-06-26T11:59:52Z")
Ignition offers the ability to have a fallback value, it’s probably just what you need ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/e/e7efbaf3e806b717475c20b1aa6492fc89413349.png) ---
**Author:** ![qversa](https://avatars.discourse-cdn.com/v4/letter/q/35a633/32.png) [@qversa](https://forum.inductiveautomation.com/u/qversa)\ **Post date:** [June 26, 2017, 10:00pm UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/3 "2017-06-26T22:00:59Z")
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. ---
**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\ **Post date:** [June 27, 2017, 12:14pm UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/4 "2017-06-27T12:14:52Z")
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? ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [June 28, 2017, 5:15am UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/5 "2017-06-28T05:15:35Z")
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. ```auto SELECT EXISTS ( SELECT MAX(charge_no) FROM zb_operating_record WHERE line = {Root Container.LineName.selectedValue} ) ``` ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [June 28, 2017, 4:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/6 "2017-06-28T16:49:04Z")
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. ---
**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\ **Post date:** [June 29, 2017, 7:30am UTC](https://forum.inductiveautomation.com/t/sql-query-error-handler/14623/7 "2017-06-29T07:30:05Z")
@paul-griffith, 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). ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d7255cdb8de362db3ee8adca91d6a831f022fe19.png) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/7ab896973e777e0d69882be52c340505180c9cdb.png) 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.