# \[FEATURE\] Query ignition dataset **URL:** \ **Category:** Ignition Early Access\ **Tags:** database\ **Created:** [October 24, 2018, 1:55pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348 "2018-10-24T13:55:40Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [October 24, 2018, 1:55pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/1 "2018-10-24T13:55:40Z")
Now I have a dataset from a querie. Since I want to limit the amount of database transactions I don’t want to query for every table, while I already have the data in the dataset. The best way I could think of is too made it possible to query a ignition dataset in the sql query part of a property. Now if you want such a thing you have to make a script or a large expression. ---
**Author:** ![ltraverse](https://avatars.discourse-cdn.com/v4/letter/l/b2d939/32.png) [@ltraverse](https://forum.inductiveautomation.com/u/ltraverse)\ **Post date:** [October 24, 2018, 2:03pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/2 "2018-10-24T14:03:12Z")
You can have your query return one large dataset which you could then pass into a template repeater. That would eliminate most scripting while allowing you to easily account for a changing dataset. ---
**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:** [October 24, 2018, 8:31pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/3 "2018-10-24T20:31:54Z")
You might find the [view() expression function](https://www.automation-pros.com/simaids/doc/expression.view.html) useful. It uses a Pseudo-SQL query to process one dataset into another. It is part of the (free) [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) module. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [October 25, 2018, 6:52am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/4 "2018-10-25T06:52:17Z")
Yes, this is usefull. But doesn’t contains everything I wanted. It should really be a nice feature to query your own datasets in ignition 8 with the normal sql query option of a property. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [October 25, 2018, 8:27am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/5 "2018-10-25T08:27:56Z")
Unfortunately, I dont even see a simple option to limit the rows to 10 or something ☹ , as in SQL you can do: select top 10 productnames,price , you cant use that in the simulation aids ---
**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:** [October 25, 2018, 11:05am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/6 "2018-10-25T11:05:45Z")
> [@joostjojo](#): > > limit the rows to 10 True enough. But you can use \_r in a WHERE clause to filter by source row number (starting with zero). So you can nest one view() expression in another to limit the row count. Something like this: ```python view("SELECT * WHERE _r<10", view("SELECT .... WHERE .... ORDER BY ....", {Root Container.SomeData})) ``` Also, you can turn on debug mode to see the jython that the view() function internally uses to execute the operation. You can cut and paste that into a script module, edit it to implement any special behavior, and call it from runScript() or objectScript() instead of using view() directly. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [October 29, 2018, 9:48am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/7 "2018-10-29T09:48:47Z")
> [@joostjojo](#): > > even see a simple option to limit the rows to 10 or something ☹ ,as in SQL you can do: select top 10 I solved it by using a script, because I have more task for my data. But thank you for the reply, this will help me in other projects. ---
**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 5, 2018, 5:20pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/8 "2018-11-05T17:20:40Z")
I’ve updated the [Simulation Aids module](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) version 1.7.8 to support the LIMIT clause in the view() function. (-: ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [November 14, 2018, 1:48pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/9 "2018-11-14T13:48:55Z")
Is it true that the Simulation Aids module does not support the like operator? ---
**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 14, 2018, 2:21pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/10 "2018-11-14T14:21:37Z")
It does not support any SQL operators or functions. Expressions in its Pseudo-SQL are python, so you can do the equivalent of LIKE in various ways. The python string method `.startswith()` is handy. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [November 20, 2018, 11:22am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/11 "2018-11-20T11:22:19Z")
> [@pturmel](#): > > .startswith() That means I have to use a python script? Because I prefer to use an expression for the readability. ---
**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 20, 2018, 1:34pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/12 "2018-11-20T13:34:15Z")
> [@joostjojo](#): > > That means I have to use a python script? No, you have to use python expressions within the PseudoSQL. The `view()` expression function writes the necessary script for you and runs it "under the hood". The view() function doesn't actually talk to a database that could execute real SQL expressions. Ignition runs java and jython, so the view() expression must do so. As noted in the documentation, if you turn on DEBUG logging for the view expression function, it will show you the script it generated. That can be a big help if your syntax throws an error. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [November 23, 2018, 10:16am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/14 "2018-11-23T10:16:05Z")
I succeeded to use order by in the view() expression. However I didn’t succeed to order by descending with this function. Is this not supported yet? ---
**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 23, 2018, 4:34pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/15 "2018-11-23T16:34:28Z")
> [@joostjojo](#): > > However I didn't succeed to order by descending with this function. No, the descending keyword is not supported. For numerical keys, you can negate the expression. ---
**Author:** ![joostjojo](https://avatars.discourse-cdn.com/v4/letter/j/ac91a4/32.png) [@joostjojo](https://forum.inductiveautomation.com/u/joostjojo)\ **Post date:** [November 28, 2018, 9:25am UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/16 "2018-11-28T09:25:07Z")
> [@pturmel](#): > > For numerical key I found another way to do so. I used the following code to sort ascending and it's working perfectly : sortDataset({Root Container.Rectangle background.AlarmData},'AlarmCount',0) Thanks for the help. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [December 11, 2018, 10:18pm UTC](https://forum.inductiveautomation.com/t/feature-query-ignition-dataset/20348/17 "2018-12-11T22:18:50Z")
Just to close the loop here - this isn’t a feature we are likely to add. +1 to using Phil’s module for this.