# Sql query and script **URL:** \ **Category:** Ignition\ **Tags:** ignition81, scripting\ **Created:** [May 2, 2022, 11:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141 "2022-05-02T23:09:18Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 2, 2022, 11:09pm UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/1 "2022-05-02T23:09:18Z")
hello, could you help me please, I want to add two values “BASIC SQL QUERY”, a “basic sql query” that returns a single value of electricity consumption and add the other “basic sql query” in a script, I attach the pictures, thank you very much. Dataset=data[‘PUESTO\_2\_INI’].getCoreResults() ``` Dataset1=data['PUESTO_2_FIN'].getCoreResults() sum = Dataset+Dataset1 data['myKey']=sum ``` ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 2, 2022, 11:42pm UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/2 "2022-05-02T23:42:08Z")
I am not totally clear on what you are trying to do, but i think what you want is [system.dataset.addColumn](https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn) ```auto Dataset1 = data['PUESTO_2_INI’'].getCoreResults() Dataset2 = data['PUESTO_2_FIN'].getCoreResults() val = [Dataset2.getValueAt(0, 0)] data['PUESTO_2_INI'] = system.dataset.addColumn(Dataset1, val, 'newCol', str) ``` ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 2, 2022, 11:58pm UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/3 "2022-05-02T23:58:25Z")
> [@josborn](#): > > I am not totally clear on what you are trying to do, but i think what you want is [system.dataset.addColumn](https://docs.inductiveautomation.com/display/DOC81/system.dataset.addColumn) Thank you very much for your answer, I want to add two values, one value is in the query "POST\_INI" and the other value is in the query "POST\_FIN". ![query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/3/330db757eba9d4917bc9e736185ca061a25f7a6d.png) ![scrips_ignition](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/6/467abaf3dbb2cf470f53fa0f8d6e056b77874a44.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 12:02am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/4 "2022-05-03T00:02:12Z")
Give my code a try, i think it will do what you want, but be sure to change it to the correct data type and appropriate column name. You can also create a new data source ‘myKey’ by changing the last line to this: ```auto data['myKey'] = system.dataset.addColumn(Dataset1, val, 'newCol', str) ``` ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 12:12am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/5 "2022-05-03T00:12:40Z")
It still does not work, I want to add these two data, and then enter the summed value to the report. At the moment of adding, I get that it is not valid to add between datasets. ![ayuda](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/f/ffd5eaf7e69a456f082f9d9ccc60093d2db44bfc.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 12:21am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/6 "2022-05-03T00:21:31Z")
The code above does not result in an error, but i am using some random junk data and the column i am adding happens to be a string. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1c8f96bd32e65bb6d22d72a06c58e6c872883dd9.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/d/ad8bebd14755c930564acf3d3bf209425fa47b21.png) ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 12:45am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/7 "2022-05-03T00:45:39Z")
> [@josborn](#): > > The code above does not result in an error, but i am using some random junk data and the column i am adding happens to be a string. I want to get the sum of "POST\_INI" and "POST\_END", the query "post\_ini" reads the variable Amps from an old date, and the query "post\_end" reads the same variable Amps with today's date, I want to add those two values by means of a script. The query returns a single value, not a column or a row, it only returns one value. ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 12:47am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/8 "2022-05-03T00:47:44Z")
I think this is a language/terminology problem. And the queries return a **dataset** not a single value. If you want to **add the values of two datasets** with exactly one row and one column each then this will do it. You don’t need the for loop, based on your description, but it will work with it. You might want to use a float instead of an int. ```auto Dataset1 = data['PUESTO_2_INI'].getCoreResults() Dataset2 = data['PUESTO_2_FIN'].getCoreResults() val = [] for row in range(Dataset1.getRowCount()): val.append(Dataset2.getValueAt(row, 0) + Dataset1.getValueAt(row, 0)) data['myKey'] = system.dataset.addColumn(Dataset1, val, 'newCol', int) ``` ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 12:54am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/9 "2022-05-03T00:54:41Z")
> [@josborn](#): > > ```auto > Dataset1 = data['PUESTO_2_INI'].getCoreResults() > Dataset2 = data['PUESTO_2_FIN'].getCoreResults() > val = [] > for row in range(Dataset1.getRowCount()): > val.append(Dataset2.getValueAt(row, 0) + Dataset1.getValueAt(row, 0)) > data['myKey'] = system.dataset.addColumn(Dataset1, val, 'newCol', int) > > ``` Thank you very much, I tried with that code but I still get "N/A", I want to add those two values that are marked with blue. ![final](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb2457b674de2fdf04fad259c01bda7dbac812a0.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 1:04am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/10 "2022-05-03T01:04:46Z")
That looks to me like a problem with your table configuration, what does the XML look like? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/7/d7c66432dbafa38191117c98d7fa2fee0f910b9e.png) ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 1:11am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/11 "2022-05-03T01:11:15Z")
Yes, I think so, please if you can help me, this is the XML ![xml](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/f/2f3075fea50cbfd28111476593be33463a0dcc2b.png) ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 1:13am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/12 "2022-05-03T01:13:30Z")
This is the code of the QUERY “PUESTO\_INI” that returns the value. ![QUERYYYY](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/1/11601dc788e40cec8cf60f990499e63e7a526b44.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 1:59am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/13 "2022-05-03T01:59:05Z")
Well, neither of your basic queries have any data, how are you getting any numbers at all, on your report? What does your configuration look like? If all you are doing is adding two values together you can do that without scripting. This is my test report, with 3 ways of adding the values together (the script is a bit different than what i have posted, i can post the alternate version if you need). ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/3/b3ae69a8d1b3b936eccea67f7adcab8235b29620.png) ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 2:20am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/14 "2022-05-03T02:20:59Z")
Thank you very much, if it adds up the value but the error I marked with yellow, what would be the problem? ![suma](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/f/9f19defc4e0cd63a2dc27c2865ead96bac2fb96c.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 2:23am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/15 "2022-05-03T02:23:37Z")
Something on line 10 is a type mismatch. ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 2:37am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/16 "2022-05-03T02:37:33Z")
Error 10 is from the code you helped me with. Adding the 3 ways you told me directly in the report does not work :c . ![error_line10](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/a/3a3afa8526fbedd33b0908d006416d79ef5f3a1d.png) ![pantalla reporte](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/c/0cb4147c76ddc80b1db0ebe0f152c19d5e9d8024.png) ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [May 3, 2022, 2:50am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/17 "2022-05-03T02:50:36Z")
That doesn’t make sense, you would get a error related to trying to coerce a float to a string: I would expect something like this to be thrown: ```auto WARN: Error invoking script.Traceback (most recent call last): File "", line 10, in updateData TypeError: can't convert 0 to java.lang.String ``` I would just delete the script entirely and type `@PUESTO_2_INI.Amps + PUESTO_2_FIN.Amps@` into the field. Just a small nitpick, why are you calling your value “Amps” if it is a measure of power usage, presumably kWh? ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 2:58am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/18 "2022-05-03T02:58:28Z")
Thank you very much I will try it. I’m still testing and I’m using the variables that come by default in Ignition, so I can do the sum. When I can do the addition I will do it with the KWH. If my attempt does not work, do you think you can advise me remotely? Thank you very much ---
**Author:** ![ALAN\_BEJARANO](https://avatars.discourse-cdn.com/v4/letter/a/74df32/32.png) [@ALAN\_BEJARANO](https://forum.inductiveautomation.com/u/ALAN_BEJARANO)\ **Post date:** [May 3, 2022, 3:20am UTC](https://forum.inductiveautomation.com/t/sql-query-and-script/59141/19 "2022-05-03T03:20:38Z")
Thank you very much for your help, I was able to add the data, it worked without scrips.