Sql query and script

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.


sum = Dataset+Dataset1

I am not totally clear on what you are trying to do, but i think what you want is system.dataset.addColumn

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)

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”.

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:

data['myKey'] = system.dataset.addColumn(Dataset1, val, 'newCol', str)

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.

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.

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.

	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.

That looks to me like a problem with your table configuration, what does the XML look like?

Yes, I think so, please if you can help me, this is the XML

This is the code of the QUERY “PUESTO_INI” that returns the value.

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).

1 Like

Thank you very much, if it adds up the value but the error I marked with yellow, what would be the problem?

Something on line 10 is a type mismatch.

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 .

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:

WARN: Error invoking script.Traceback (most recent call last):
  File "<function:updateData>", 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?

1 Like

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

Thank you very much for your help, I was able to add the data, it worked without scrips.