# SQL Command Available

**URL:** <https://forum.inductiveautomation.com/t/sql-command-available/19212>\
**Category:** Ignition\
**Created:** [August 11, 2018, 2:31pm UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212 "2018-08-11T14:31:43Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevin.jones](https://avatars.discourse-cdn.com/v4/letter/k/e9c0ed/32.png) [@kevin.jones](https://forum.inductiveautomation.com/u/kevin.jones)\
**Post date:** [August 11, 2018, 2:31pm UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212/1 "2018-08-11T14:31:43Z")

</div>

I’m limping along trying to get some various SQL queries to work and have issues with trying to use IF or CASE in the SELECT statement. Eg:

SELECT B28H as State, Count (B28) as Min  
, SUM ( IF B28H IN (1,2,3,5) THEN 1 ELSE 0 END) AS UpTime  
, SUM ( IF B28H IN (1,2,3,5) THEN 1 ELSE 0 END) / Count (B28) AS PercentUpTime  
FROM Cell\_Status  
WHERE t\_stamp BETWEEN ‘2018-08-03 07:00:00’ AND ‘2018-08-07 19:00:00’  
GROUP BY B28H

Is the SQL command set in Ignition limited? Does it support conditional logic in the SELECT statement? I scanned the documentation but didn’t see anything specifying what can/can’t do in Ignition.

Any pointers appreciated.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 11, 2018, 2:58pm UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212/2 "2018-08-11T14:58:45Z")

</div>

Ignition itself doesn’t process the query. It passes it to the DB via JDBC, and the DB parses it. Run your query in the DB’s management tool to see if the syntax is supported.

---

<div class="post-metadata">

**Author:** ![kevin.jones](https://avatars.discourse-cdn.com/v4/letter/k/e9c0ed/32.png) [@kevin.jones](https://forum.inductiveautomation.com/u/kevin.jones)\
**Post date:** [August 11, 2018, 5:24pm UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212/3 "2018-08-11T17:24:58Z")

</div>

OK. I didn’t realize that. Does the DB query browser in Ignition work the same way or is it possible that a query doesn’t work in the browser would actually work when embedded in a control or something in an Ignition application window or script?

The awkward thing is I don’t have access to the SQL Server instance that hosting the DB - it on a remote machine and I just have the connection setup. In this scenario any suggestions on how to develop/test the SQL queries?

Thanks for the prompt reply!

---

<div class="post-metadata">

**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\
**Post date:** [August 11, 2018, 5:34pm UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212/4 "2018-08-11T17:34:44Z")

</div>

> [@kevin.jones](#):
>
> The awkward thing is I don't have access to the SQL Server instance that hosting the DB - it on a remote machine and I just have the connection setup.

You can get the db connection details from the "Gateway web page/Database Connections" page. You need password to configure and access the remote db from management studio. I mean, atleast a read only user account/access shall be given for you.

Edited:  
Better, setup a test db with sample data on your local machine for testing the queries.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [August 12, 2018, 1:03am UTC](https://forum.inductiveautomation.com/t/sql-command-available/19212/5 "2018-08-12T01:03:14Z")

</div>

> [@kevin.jones](#):
>
> OK. I didn't realize that. Does the DB query browser in Ignition work the same way or is it possible that a query doesn't work in the browser would actually work when embedded in a control or something in an Ignition application window or script?

The query browser in the designer is probably the best choice for testing, as it uses the JDBC connections just like components or scripts would, other than the lack of substitution parameters. This can matter in cases where DB's own tool will run scripts -- JDBC won't.