# \[SOLVED\] Parse a table returned from a named query **URL:** \ **Category:** Ignition\ **Created:** [June 23, 2020, 10:29pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950 "2020-06-23T22:29:52Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\ **Post date:** [June 23, 2020, 10:29pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/1 "2020-06-23T22:29:52Z")
What is the syntax to call a named query and parse the results in Ignition scripting? ``` tbl = system.db.runNamedQuery("projectName", "somePath/selectStuff", {}) if tbl.getRowCount() > 0: keyID = tbl[0][0] someText= tbl[0][1] ``` My named query succeeds in testing but when I implement the above in a script I get the following error: ``` Error type: Error message : 'com.inductiveautomation.ignition.gateway.datasource.BasicStreamingDataset' object is unsubscriptable ``` Any thoughts on how to fix this issue? ---
**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:** [June 23, 2020, 10:41pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/2 "2020-06-23T22:41:17Z")
If you want to access individual elements like it’s a nested list, you need to change the `Dataset` returned by `runNamedQuery` into a PyDataset, via the appropriately named [`system.dataset.toPyDataset`](https://docs.inductiveautomation.com/display/DOC80/system.dataset.toPyDataSet) function. See the `Datasets` page for more background on the difference between the two: [https://docs.inductiveautomation.com/display/DOC80/Datasets](https://docs.inductiveautomation.com/display/DOC80/Datasets) ---
**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\ **Post date:** [June 24, 2020, 2:01pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/3 "2020-06-24T14:01:44Z")
So a named query returns a regular dataset? Am I understanding that correctly? What does an in-line query return? ``` sql = 'SELECT TOP(1) keyID, someText FROM someTable ORDER BY someText ASC' args = [] tbl = system.db.runPrepQuery(sql, args, db) return tbl ``` I ask because this is the code I was converting from a script definition to a named query. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 24, 2020, 2:19pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/4 "2020-06-24T14:19:12Z")
system.db.runPrepQuery returns a PyDataset ---
**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\ **Post date:** [June 24, 2020, 2:27pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/5 "2020-06-24T14:27:52Z")
So the named query executes the IN-LINE code and returns a pydataset but then the named query is converting it to a regular dataset? That’s mildly annoying to contemplate but thanks for the info. Anyways I was able to fix my problem by converting the regular dataset back into a pydataset using the line: ``` pyDataSet = system.dataset.toPyDataSet(tbl) ``` ---
**Author:** ![martinel3746](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@martinel3746](https://forum.inductiveautomation.com/u/martinel3746)\ **Post date:** [June 24, 2020, 8:34pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/6 "2020-06-24T20:34:43Z")
I’ve had good success with: ```auto for row in range( tbl.rowCount ): keyId = tbl.getValueAt( row, 0 ) # better to use column names someText = tbl.getValueAt( row, 1 ) # 2nd column serialNum = tbl.getValueAt( row, 'SERIAL_NBR' ) # sample column name # do your workload / logic here ... ``` That works with the dataset returned by the Named Query without duplicating all the data into another datatype. You can use `system.dataset.getColumnHeaders( tbl )` to get a List with the exact spelling and capitalization of the columns returned by the Named Query. ---
**Author:** ![Greg.Bogard](https://avatars.discourse-cdn.com/v4/letter/g/76d3ee/32.png) [@Greg.Bogard](https://forum.inductiveautomation.com/u/Greg.Bogard)\ **Post date:** [September 29, 2020, 2:11pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/7 "2020-09-29T14:11:30Z")
the toPyDataSet function solved my problem reading from a dataset returned from a named Query. Thank you very much for the post. #---------------------------------------------------------------------------------------------------------------- `Preformatted text` parameters = {“LOT\_NUMBER”:(self.session.custom.strLotNumber)} ds = system.db.runNamedQuery(“nq\_spGetBrand”, parameters) pyDataSet = system.dataset.toPyDataSet(ds) if pyDataSet.rowCount \> 0: strBrand = str(pyDataSet[0][‘BRAND’]) `Preformatted text` #---------------------------------------------------------------------------------------------------------------- ---
**Author:** ![Esmeralda\_Lopez](https://avatars.discourse-cdn.com/v4/letter/e/fbc32d/32.png) [@Esmeralda\_Lopez](https://forum.inductiveautomation.com/u/Esmeralda_Lopez)\ **Post date:** [July 21, 2022, 7:57pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/8 "2022-07-21T19:57:33Z")
I want to convert a RunNamedQuery to a dataset and send data to export too CSV ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 21, 2022, 8:29pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/9 "2022-07-21T20:29:15Z")
Just configure the Named Query to return a dataset ---
**Author:** ![Esmeralda\_Lopez](https://avatars.discourse-cdn.com/v4/letter/e/fbc32d/32.png) [@Esmeralda\_Lopez](https://forum.inductiveautomation.com/u/Esmeralda_Lopez)\ **Post date:** [July 21, 2022, 9:16pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/10 "2022-07-21T21:16:51Z")
How? Is the part I don’t understand ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [July 21, 2022, 9:34pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/11 "2022-07-21T21:34:38Z")
When you configure a query binding there is an option to set the return format. By default this is set to auto. If you wanting to do this in script then the type returned by `system.db.runNamedQuery()` is determined by the Query Type of the Named Query. If the Named Queries query type is set to query then the function will return a dataset. ---
**Author:** ![Esmeralda\_Lopez](https://avatars.discourse-cdn.com/v4/letter/e/fbc32d/32.png) [@Esmeralda\_Lopez](https://forum.inductiveautomation.com/u/Esmeralda_Lopez)\ **Post date:** [July 21, 2022, 9:53pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/12 "2022-07-21T21:53:49Z")
I have this and this save a document with two sheets for the both tables, I want to add another sheet for the runNamedQuery : ```auto namedQuery = system.db.runNamedQuery("EstadisticasPedidos/FiltroLineasPedido3",{}) table =event.source.parent.getComponent("tblHistorialPedidos") table2=event.source.parent.getComponent("tblHistorialPedidosDetalles") from com.inductiveautomation.ignition.common import BasicDataset dataset= BasicDataset(table.data) dataset2= BasicDataset(table2.data) dataset.setColumnNames(["F.Pedido","Confirm G","No. Pedido","No. Hoja","No. Ruta","F.Arranque","F. Activo","F. Finalizado","ID Ruta","T. Líneas","Cod. Caja","Estado Pedido"]) dataset2.setColumnNames(["No. Pedido","Nombre Producto","No. Sector","No. Canal","Código Producto","Cantidad","Surtido","Detalle Surtido"]) sp= system.dataset.dataSetToExcel(1,[dataset,dataset2]) filePath= system.file.saveFile(u'Archive.xls') if filePath != None: system.file.writeFile(filePath,sp) system.net.openURL("file:///"+filePath.replace('\\','/')) ``` ---
**Author:** ![bmeyers](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bmeyers/32/37453_2.png) [@bmeyers](https://forum.inductiveautomation.com/u/bmeyers)\ **Post date:** [July 22, 2022, 1:59pm UTC](https://forum.inductiveautomation.com/t/solved-parse-a-table-returned-from-a-named-query/35950/13 "2022-07-22T13:59:09Z")
This code won’t work yet but should get you pretty close. The named query returns a basic dataset so no need to convert it. Then add the column names for that dataset. Then pass it to the variable sp. ```auto table =event.source.parent.getComponent("tblHistorialPedidos") table2=event.source.parent.getComponent("tblHistorialPedidosDetalles") from com.inductiveautomation.ignition.common import BasicDataset dataset= BasicDataset(table.data) dataset2= BasicDataset(table2.data) #Named query returns a basic dataset dataset3 = system.db.runNamedQuery("EstadisticasPedidos/FiltroLineasPedido3",{}) dataset.setColumnNames(["F.Pedido","Confirm G","No. Pedido","No. Hoja","No. Ruta","F.Arranque","F. Activo","F. Finalizado","ID Ruta","T. Líneas","Cod. Caja","Estado Pedido"]) dataset2.setColumnNames(["No. Pedido","Nombre Producto","No. Sector","No. Canal","Código Producto","Cantidad","Surtido","Detalle Surtido"]) #Insert correct columns returned from dataset3 dataset3.setColumnNames(["Column1", "Column2", "Column3", etc]) sp= system.dataset.dataSetToExcel(1,[dataset,dataset2, dataset3]) filePath= system.file.saveFile(u'Archive.xls') if filePath != None: system.file.writeFile(filePath,sp) system.net.openURL("file:///"+filePath.replace('\\','/')) ```