SQL query from Dataset tag

Ignition
1

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.

2

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() function of the Integration Toolkit third-party module (mine).

(-:

Edit: Link fixes due to module rename...

3

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.

4

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.

1 Like