system.db.runQuery formating question

Is there a way to format a large query using system.db.runQuery so that it is easy to read ?

For example I would like my query to look something like this ,

system.db.runQuery(“Select something,
Case when this = 1 then ‘Loading’
when this = 2 then ‘Start flux capacitor’
when this = 3 then ‘Operator Check’
end as ‘this’,
T_stamp
from database
order by t_stamp”)

I have a query with 10 case statements on of which has 35 conditions. I don’t want to run the query together like it is in the example below as that would be a nightmare for the next guy to maintain.

system.db.runQuery(“Select something,Case when this = 1 then ‘Loading’ when this = 2 then ‘Start flux capacitor’ when this = 3 then ‘Operator Check’ end as ‘this’, T_stamp from database order by t_stamp”)

Why not move the mapping to another table?

SELECT database.something, maptable.state FROM database join maptable on database.this = maptable.index

Just have a separate lookup table for each of your “case statements”?

You could also just pull back “this” as an integer, and use that as an index into a StringArray tag.

query = """
	Select something,
	Case when this = 1 then 'Loading'
		when this = 2 then 'Start flux capacitor'
		when this = 3 then 'Operator Check'
		end as 'this',
	T_stamp 
	from database
	order by t_stamp
"""
system.db.runPrepQuery(query,[])
1 Like

I usually do something like this when I have a long query or if I’m trying to keep SQL SYNTAX legible for the next person coming in.

query = \
"""
SELECT
	SOMETHING,
	CASE WHEN THIS = 1 THEN 'Loading'
		 WHEN THIS = 2 THEN 'Start flux capacitor'
		 WHEN THIS = 3 THEN 'Operator Check'
		 END as 'this',
	T_STAMP
FROM DATABASE
ORDER BY T_STAMP
"""

system.db.runQuery(query)
2 Likes

Thanks everyone for the quick responses,

Kyle it would defiantly be easier to use another table but unfortunately we have a little IT/OT resistance going on so i’m trying to keep my DB footprint as small as possible plus I don’t have access to the SQL workbench. Thank you for your help.

Japark,Samnreru thats exactly what I was looking for thank you !

1 Like

One way to possibly solve (or mitigate) these things is to use the free Database Module. This gives you an embedded database in Ignition that you can have full control of. And it gives you a GUI tool (called the Database Editor) to create/edit your database schemas.

You don't need SQL Workbench for a lot of common things if you have the Database Editor project. It works with MySQL. I originally made the Database Editor project because I didn't have access to MySQL Workbench either.

I like Nick’s module, but you can’t join across database connections, so it doesn’t help you in this case. Consider reprocessing the data after you retrieve it. I would put the substitutions into a project script, like so:

states = {1: 'Loading', 2: 'Start flux capacitor', 3: 'Operator Check'}

Then reprocess after querying like so, using the dictionary’s get(key, default) method:

pyds = system.db.runQuery("""SELECT SOMETHING, this FROM DB ORDER BY T_STAMP""")
heads = pyds.underlyingDataset
rows = [[row[0], project.myScript.states.get(row[0], str(row[0]))] for row in pyds]
newds = system.dataset.toDataSet(heads, rows)

The reprocessing can be done in bindings with the view() function from my Simulation Aids module if that is more convenient. You’d put the raw query on a custom dataset property, and then feed that to the view() expression function on the target dataset property like so:

view("SELECT SOMETHING, project.myScript.states.get(this, str(this)) As this",
  {Root Container.SomeComponent.rawData})