# SQL query from Dataset tag

**URL:** <https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017>\
**Category:** Ignition\
**Created:** [July 30, 2018, 4:46pm UTC](https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017 "2018-07-30T16:46:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![harold.bruulsema](https://avatars.discourse-cdn.com/v4/letter/h/e79b87/32.png) [@harold.bruulsema](https://forum.inductiveautomation.com/u/harold.bruulsema)\
**Post date:** [July 30, 2018, 4:46pm UTC](https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017/1 "2018-07-30T16:46:38Z")

</div>

I have a Dataset tag I call "RouteSourceNames", it has columns "RouteNum", "RouteName", and "Route2" (boolean). I have populated this dataset with 21 rows by importing from excel.  
I put a Dropdown List on my display, I can link Data to this tag no problem, it works fine, functions as expected.  
Now my dilemma: I want to only show a subset of the dataset - I only want to show the rows where "Route2" is True.  
So instead of binding to a Tag, I attempt to bind to a SQL Query, where i enter

> SELECT RouteNum, RouteName  
> FROM {RouteSourceNames}  
> WHERE Route2=True  
> I get error  
> Exception: Error running query:  
> SQLQuery(query=SELECT RouteNum, RouteName  
> FROM Dataset [21R ⅹ 5C]  
> WHERE Route2=True, database=Ignition\_SQL\_App001)@0ms  
> On: ROUTE2.Root Container.cboSource.data  
> caused by GatewayException: Invalid object name 'Dataset'.  
> caused by SQLServerException: Invalid object name 'Dataset'.

> Ignition v7.9.8 (b2018060714)  
> Java: Oracle Corporation 1.8.0\_161

I think I am missing something in the syntax to refer to the dataset tag. The only examples I can find work with a SQL table, i'd prefer to avoid creating a SQL table if 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:** [July 30, 2018, 8:49pm UTC](https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017/2 "2018-07-30T20:49:01Z")

</div>

Ignition doesn't process SQL itself. It is always passed to a database to execute. The closest you can come to this in Ignition is to use Pseudo-SQL in the [view()](https://www.automation-pros.com/toolkit/doc/scripting.html#view) function of the [Integration Toolkit](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-integration-toolkit) third-party module (mine).

(-:

Edit: Link fixes due to module rename...

---

<div class="post-metadata">

**Author:** ![harold.bruulsema](https://avatars.discourse-cdn.com/v4/letter/h/e79b87/32.png) [@harold.bruulsema](https://forum.inductiveautomation.com/u/harold.bruulsema)\
**Post date:** [July 31, 2018, 5:44pm UTC](https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017/3 "2018-07-31T17:44:47Z")

</div>

OK, thanks. I’ll have to think about it some more, whether I want to install an additional module on the server (making it non-standard), or if it is worth creating some more tables on my SQL server.

---

<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:** [July 31, 2018, 6:14pm UTC](https://forum.inductiveautomation.com/t/sql-query-from-dataset-tag/19017/4 "2018-07-31T18:14:32Z")

</div>

You can always write jython that will take one dataset and process the rows to create a new dataset. You can even install the Sim Aids module temporarily, use the view() function to filter the way you want, and put the view() function’s logger into DEBUG mode to see what jython code it generated.