# system.db.runQuery formating question

**URL:** <https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851>\
**Category:** Ignition\
**Created:** [November 1, 2017, 8:21pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851 "2017-11-01T20:21:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![trd79](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/trd79/32/4242_2.png) [@trd79](https://forum.inductiveautomation.com/u/trd79)\
**Post date:** [November 1, 2017, 8:21pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/1 "2017-11-01T20:21:18Z")

</div>

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”)

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [November 1, 2017, 8:32pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/2 "2017-11-01T20:32:10Z")

</div>

Why not move the mapping to another table?

```auto
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”?

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [November 1, 2017, 8:32pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/3 "2017-11-01T20:32:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jpark](https://avatars.discourse-cdn.com/v4/letter/j/a88e57/32.png) [@jpark](https://forum.inductiveautomation.com/u/jpark)\
**Post date:** [November 1, 2017, 9:17pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/4 "2017-11-01T21:17:02Z")

</div>

```auto
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,[])

```

---

<div class="post-metadata">

**Author:** ![samngeru](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samngeru/32/6007_2.png) [@samngeru](https://forum.inductiveautomation.com/u/samngeru)\
**Post date:** [November 1, 2017, 9:19pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/5 "2017-11-01T21:19:51Z")

</div>

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.

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![trd79](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/trd79/32/4242_2.png) [@trd79](https://forum.inductiveautomation.com/u/trd79)\
**Post date:** [November 2, 2017, 11:46am UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/6 "2017-11-02T11:46:08Z")

</div>

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 !

---

<div class="post-metadata">

**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\
**Post date:** [November 2, 2017, 12:11pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/7 "2017-11-02T12:11:06Z")

</div>

> [@trd79](#):
>
> 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.

One way to possibly solve (or mitigate) these things is to use the free [Database Module](http://www.perfectabstractions.com/database-module.html). 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](http://www.perfectabstractions.com/database-editor.html). It works with MySQL. I originally made the Database Editor project because I didn't have access to MySQL Workbench either.

---

<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:** [November 2, 2017, 1:07pm UTC](https://forum.inductiveautomation.com/t/system-db-runquery-formating-question/15851/8 "2017-11-02T13:07:47Z")

</div>

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:

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

```

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

```auto
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](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) 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:

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

```