# Way to view the raw SQL of a query binding?

**URL:** <https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [May 17, 2022, 3:33pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674 "2022-05-17T15:33:22Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 3:33pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/1 "2022-05-17T15:33:22Z")

</div>

Just had an issue where I had to optimize a window. After searching through every component/root container property that had a query binding, I was able to remove a couple of redundant queries by putting them into a single query and grabbing from that via expressions.

I would like to be able to automate this a bit for some other slow windows that I have in this project (and other projects).

If possible I’d like to:

1. See if any of properties of a component or root container have a SQL Query binding (I assume there must be someway this is possible as the Designer knows and marks components with db with a  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/3/f30f9e5ee4957efd452478eb1c92c1ff7839e29d.png))

2. If a component (or rc property) is a SQL binding - to get the SQL that would run.  
For instance if a SQL Binding is `SELECT * FROM someTable WHERE id = {Root Container.tableId}` and the root container property `tableId` = 7, then I would see Component X has it’s data property set by the SQL Query `SELECT * FROM someTable WHERE id=7`. I figure this one is probably a bit harder. If I can’t get the values filled in but I could still see `SELECT * FROM someTable WHERE id={Root Container.tableId}` that would still be acceptable to me.

I have a lot of windows that are poorly optimized with redundant queries/un-indexed queries and I would very much like to be able to write a script, get all the queries that are run on it, so I could then do some automation with testing the speed of the query/checking the explain statement to look for full table scans/see where queries can be combined etc. Is this possible?

---

<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:** [May 17, 2022, 3:37pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/2 "2022-05-17T15:37:27Z")

</div>

Ready to go down a rabbit hole to ~~WonderLand~~ _FPMIWindowLand_?

Start here:

[https://forum.inductiveautomation.com/search?q=InteractionController%20@pturmel%20order:latest](https://forum.inductiveautomation.com/search?q=InteractionController%20@pturmel%20order:latest)

References start here:

[https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.1/com/inductiveautomation/factorypmi/application/binding/InteractionController.html](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.1/com/inductiveautomation/factorypmi/application/binding/InteractionController.html)

---

<div class="post-metadata">

**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:** [May 17, 2022, 3:37pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/3 "2022-05-17T15:37:58Z")

</div>

The `QueryManager` logger in the client on TRACE might be helpful.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 3:49pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/4 "2022-05-17T15:49:34Z")

</div>

Ok I think this is the rabbit hole I have to jump down at least for the rest of the day.

I am testing it on a single component that I know has a data query binding on it.

Here is my code

```auto
import com.inductiveautomation.factorypmi.application.binding

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

IC = com.inductiveautomation.factorypmi.application.binding.InteractionController
results = IC.getAllAdaptersForTarget(chatTable)

```

but this is telling me `TypeError: getAllAdaptersForTarget(): expected 2 args; got 1`

But according to the documentation it only seems to take a single argument `getAllAdaptersForTarget​(java.awt.Component target)` What am I doing wrong/misreading?

---

<div class="post-metadata">

**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:** [May 17, 2022, 3:52pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/5 "2022-05-17T15:52:52Z")

</div>

You need an interaction controller _instance_ - you can’t call `getAllAdaptersForTarget` statically (the second argument it would need to do that would be an instance to pass as `self`).

FPMIWindow has a `getInteractionController()` method.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 4:11pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/6 "2022-05-17T16:11:51Z")

</div>

My (lack of) java knowledge is showing 😅

I followed what you said and now have things sort of working though things don’t seem right - assuming it’s a problem with my code.

```auto
import com.inductiveautomation.factorypmi.application.FPMIWindow

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

FPMIWindow = com.inductiveautomation.factorypmi.application.FPMIWindow("Pop_HelpDesk")
IC = FPMIWindow.getInteractionController()
results_1 = IC.getAllAdaptersForTarget(chatTable)
results_2 = IC.getInteractionDescriptorsWithSource(chatTable)
results_3 = IC.getInteractionDescriptorsWithTarget(chatTable)
results_4 = IC.getPropertyAdapter(chatTable, 'data')

```

The length of results\_1, results\_2, and results\_3 are all 0. I know that the `data` prop has a query binding on it so I would have though that would have come through on results\_1 but it does not, and results\_4 is `NoneType`.

Also based on my reading, I one of results\_2/3 should have given me something as the query itself has `ORDER BY m.idx {Root Container.PTable_Chat.orderByClause}` and these InteractionControllers are the way that Ignition provides that dropdown when deleting something. As in if I tried to delete `{Root Container.PTable_Chat.orderByClause}` I would get a warning my data binding relied on it etc which is why I would have thought results\_2 or 3 would have gave me something.

What am I doing wrong this time?

---

<div class="post-metadata">

**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:** [May 17, 2022, 4:12pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/7 "2022-05-17T16:12:59Z")

</div>

> [@bkarabinchak.psi](#):
>
> `FPMIWindow = com.inductiveautomation.factorypmi.application.FPMIWindow("Pop_HelpDesk")`

That's constructing a _new_ window. What's the type of `win` when you retrieve it with `getWindow()`?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 4:14pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/8 "2022-05-17T16:14:15Z")

</div>

Ahh I see, `<type 'com.inductiveautomation.factorypmi.application.FPMIWindow'>`

```auto
import com.inductiveautomation.factorypmi.application.FPMIWindow

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

chatTable = rc.getComponent('PTable_Chat')

IC = win.getInteractionController()
results_1 = IC.getAllAdaptersForTarget(chatTable)
results_2 = IC.getInteractionDescriptorsWithSource(chatTable)
results_3 = IC.getInteractionDescriptorsWithTarget(chatTable)
results_4 = IC.getPropertyAdapter(chatTable, 'data')

```

Is giving me what I expect now. Now I can really get lost in the weeds.

---

<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:** [May 17, 2022, 4:51pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/9 "2022-05-17T16:51:37Z")

</div>

I look away for a bit and all hell breaks loose. 😁

As Paul hints, you don’t need to import FPMIWindow. You might want to import some of the `Adapter` types to use in `isinstance` calls. Jython will generally auto-map all the other stuff you need.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 4:58pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/10 "2022-05-17T16:58:34Z")

</div>

I was able to use this script

```auto
import com.inductiveautomation.factorypmi.application.FPMIWindow
import com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter

win = system.gui.getWindow("Pop_HelpDesk")
rc = win.getRootContainer()

results = {'rc':[]}

IC = win.getInteractionController()

rcAdapters = IC.getAllAdaptersForTarget(rc)

for adapter in rcAdapters:
	if isinstance(adapter, com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter):
		propName = adapter.getTargetPropertyName()
		query = adapter.getPullQuery().getQuery()
		results['rc'].append((propName, query))

for component in rc.getComponents():
	allAdapters = IC.getAllAdaptersForTarget(component)
	for adapter in allAdapters:
		if isinstance(adapter, com.inductiveautomation.factorypmi.application.binding.SQLPropertyAdapter):
			propName = adapter.getTargetPropertyName()
			query = adapter.getPullQuery().getQuery()
			results[component.name] = (propName, query)

```

To get me all the SQL Bindings for my root container properties and components into a dictionary so from here now I can write some functions that analyze this data.

Thanks!

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 5:11pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/11 "2022-05-17T17:11:32Z")

</div>

Does Ignition have a SQL Parser? Given the contextual highlighting on query bindings/named queries/the database browser, I am hopeful. Was looking to grab table names from these queries with regex but that seems ill-advised after a little research if you can use a SQL Parser instead. Is that something that is part of the ecosystem? I tried searching the documentation but all I can find is  
[QueryParser](https://files.inductiveautomation.com/sdk/javadoc/ignition81/8.1.1/com/inductiveautomation/ignition/common/db/namedquery/QueryParser.html#parse(java.lang.String)) which doesn’t quite do what I need (which is to grab the tables out of the queries).

---

<div class="post-metadata">

**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:** [May 17, 2022, 5:15pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/12 "2022-05-17T17:15:21Z")

</div>

Nothing that would readily help you, I don’t think. The code editors used aren’t particular smart.

---

<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:** [May 17, 2022, 5:16pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/13 "2022-05-17T17:16:13Z")

</div>

Consider rewriting your imports like so:

```auto
from com.inductiveautomation.factorypmi.application.binding import SQLPropertyAdapter

```

Then you can just use `SQLPropertyAdapter` where needed.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/14 "2022-05-17T17:20:58Z")

</div>

Fair enough. Figured it was a long shot as it doesn’t seem like Ignition has any reason to need to know this, it just needs to pass it onto the database.

@pturmel thanks, that is much cleaner.

Final script I use in case anyone wants to use it:

```auto
def getAllQueriesInWindow(windowName):
	win = system.gui.getWindow(windowName)
	rc = win.getRootContainer()
	results = {'rc':{}}
	IC = win.getInteractionController()
	rcAdapters = IC.getAllAdaptersForTarget(rc)
	
	for adapter in rcAdapters:
		if isinstance(adapter, SQLPropertyAdapter):
			propName = adapter.getTargetPropertyName()
			query = adapter.getPullQuery().getQuery().lower().replace('\n', ' ')
			results['rc'][propName]= query
	
	for component in rc.getComponents():
		allAdapters = IC.getAllAdaptersForTarget(component)
		for adapter in allAdapters:
			if isinstance(adapter, SQLPropertyAdapter):
				propName = adapter.getTargetPropertyName()
				query = adapter.getPullQuery().getQuery().lower().replace('\n', ' ')
				if component.name not in results.keys():
					results[component.name]={}
				results[component.name][propName]= query
	return results

```

---

<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:** [May 17, 2022, 6:13pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/15 "2022-05-17T18:13:22Z")

</div>

You can probably simplify that a bit more with this utility function:

```auto
# Recursively scan a VisionContainer for VisionComponents and inner
# VisionComponents, calling a given routine for each child encountered.
#
def forEachChild(container, func):
	func(container)
	try:
		children = container.getComponents()
  except:
		return
	for component in children:
		forEachChild(component, func)

```

Pass it your root component. This will catch any grouped components and contents of nested containers.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 17, 2022, 6:29pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/16 "2022-05-17T18:29:38Z")

</div>

Took me a second to figure it out but I got it working! Thanks. I think I should probably do the full component path if possible as my dictionary key since now I am recursively going into containers and it will just make things more clear.

Anyways - new updated version:

```auto
import com.inductiveautomation.factorypmi.application.FPMIWindow
from com.inductiveautomation.factorypmi.application.binding import SQLPropertyAdapter
import re

def forEachChild(container, func):
	func(container)
	try:
		children = container.getComponents()
	except:
		return
	for component in children:
		forEachChild(component, func)

def getAllQueriesInWindow(windowName):
	win = system.gui.getWindow(windowName)
	rc = win.getRootContainer()
	IC = win.getInteractionController()
	results = {}
	def findAllAdapterProperties(container):
		allAdapters = IC.getAllAdaptersForTarget(container)
		for adapter in allAdapters:
			if isinstance(adapter, SQLPropertyAdapter):
				propName = adapter.getTargetPropertyName()
				# Want to sanitie queries to be consistant
				# the re.sub gets rid of extra whitespaces
				query = re.sub('\s\s+', ' ', adapter.getPullQuery().getQuery().lower().replace('\n', ' '))
				if container.name not in results.keys():
					results[container.name]={}
				results[container.name][propName]= query
	forEachChild(rc, findAllAdapterProperties)
	return results

```

And to use in conjunction with this I wrote a few diagnostics

```auto
def getQueryBindingCountInWindow(windowName):
	results = getAllQueriesInWindow(windowName)
	total=0
	for key, value in results.items():
		total+=len(value)
	return total

def detectPotentialDuplicateQueries(windowName):
	# First Niave Method of finding potential duplicates. Return data where the same database table is in multiple FROM clauses
	# as a decent first pass to see where queries may be duplicated or potentially consolidated
	results = getAllQueriesInWindow(windowName)
	potentialDuplicates = {}
	for component, componentData in results.items():
		for prop, query in componentData.items():
# print "working on %s %s %s"%(component, prop, query)
			tableSearchResult = re.search("from\s+([^ ,]+)(?:\s*,\s*([^ ,]+))*\s+", query)
			if tableSearchResult is not None:
				tableName = tableSearchResult.group(1)
# print "found table %s"%(tableName)
				if tableName not in potentialDuplicates.keys():
					potentialDuplicates[tableName] = []
				potentialDuplicates[tableName].append((component, prop, query))
	for k, v in potentialDuplicates.items():
		if len(v) == 1:
			del potentialDuplicates[k]
	return potentialDuplicates

```

From the first function I found out my window has to evaluate 24 SQL Bindings wihch seems like too many, and from my second function I realized it’s because I have 5 that are basically the same query from the same exact table, so I was able to get rid of a lot of redundancy. Thanks @paul-griffith @pturmel

Now to go run this on some of the first windows I ever made in Igntion that made it to production 🙃

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 19, 2022, 1:35pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/17 "2022-05-19T13:35:36Z")

</div>

This has been working nicely. I am trying it on a new window I made that is all Named Queries, to see if there’s instances where I am calling the same Named Query in two or more different spots for consolidation.

When I was debugging/figuring out how to parse out named query adapters (turned out to be NamedQueryAdapter lol) I notice something weird on my root container.

I am running this script in script console on my new window which at one point threw a AttributeError, so this is how I have it currently -

```auto
win = system.gui.getWindow("Pop_VendorPO")
rc = win.getRootContainer()
IC = win.getInteractionController()
rcAdapters = IC.getAllAdaptersForTarget(rc)
for adapter in rcAdapters:
	try:
		print adapter.getTargetPropertyName()
	except AttributeError:
		print adapter

```

results in

```auto
initialData
_out_projectId
projectData
datasetsLoaded
vendorData
_out_vendorPONumber
vendorId
addMode
_out_customShipViaMethod
_out_initialInventoryList
allowCalcUpdates
previousPOsForThisProject
locationChoices
com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter@68ca03fd

```

So I have some ActionAdapter that does not have a targetPropertyName at all on my root container - a ghost action adapter? My root container does have a property change script so I don’t know if maybe if it represents that, but I didn’t think change script ever stopped you from deleting a binding via the InteractionController (in the way that if you try to delete a custom property used in another expression - you ARE stopped). So I don’t think its that?

Here are my root container properties which all match up, except obviously to `com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter`  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/f/0f8eff1edcc0846df25ce7a4fca5763045f0ec41.png)

Any ideas on what this could be? Expected or unexpected behavior?

---

<div class="post-metadata">

**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:** [May 19, 2022, 2:35pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/18 "2022-05-19T14:35:44Z")

</div>

ActionAdapter says:

```auto
/*
 * This is the implementation of event scripts associated with Vision components
 */

```

---

<div class="post-metadata">

**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\
**Post date:** [May 19, 2022, 2:54pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/19 "2022-05-19T14:54:19Z")

</div>

> [@bkarabinchak.psi](#):
>
> has to evaluate 24 SQL Bindings wihch seems like too many, and from my second function I realized it’s because I have 5 that are basically the same query from the same exact table, so I was able to get rid of a lot of redundancy. Th

Once you found your redundancy, how did you clean them up?  
Like if 5 are basically the same, how did you eliminate 4?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [May 19, 2022, 3:00pm UTC](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674/20 "2022-05-19T15:00:43Z")

</div>

My situation was I had 4 queries like the following -

```auto
SELECT column1
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}

```

and another would look like

```auto
SELECT column3
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}

```

etc. Slightly more complicated, some had `CASE` statements for instance, but they all had the same exact WHERE clause and were selecting from the same table.

So I was able to curate all of them into a single SELECT clause like

```auto
SELECT 
column1, 
column3,
CASE WHEN something THEN something ELSE somethingElse END as 'something',
CASE WHEN something2 THEN something2 ELSE somethingElse2 END as 'something2',
FROM someTable
WHERE parentThreadId={Root Container.threadId} AND userId = {client/userId}

```

So I put this into a single dataset, and then in those 4 spots where I was previously running a query for a single bit of data, I turned them into expressions like `try({Root Container.newDataSet}[0, 'neededColumn'], 'fallbackValue')`.

Now a single query grabs all the column data that is needed, and the properties try to grab from that dataset, instead of running their own query.

[Next page](https://forum.inductiveautomation.com/t/way-to-view-the-raw-sql-of-a-query-binding/59674.md?page=2)