SQL Command Available

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.

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.

1 Like

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!

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.

1 Like

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.

2 Likes