fdmAPI.executeQuery - return Select SUM

kafka89

Hi All,

I have an issue similar to the one sorted here -

The difference is that I need to output sum of Amount into variable.

Can someone help

  try:         sqlQuery = "SELECT SUM(TDATASEG_T.AMOUNT) FROM TDATASEG_T WHERE TDATASEG_T.LOADID = ? "        loadId = fdmContext["LOADID"]                    params = [loadId]                     rs = fdmAPI.executeQuery(sqlQuery, params)        var1 = ???        fdmAPI.logInfo(str(var1))        fdmAPI.closeResultSet(rs)    except Exception, err:        fdmAPI.logError("Something went wrong" + str(err))

JohnGoodwin

I have tested and it looks to be working

If you are happy with the type being a string for just writing to the log then you could use getString(int) or stick with getString(String)

Alternatively you can convert the type.of your choice

The list of methods available for ResultSet are available in the Java docs.

Select whichever way works best for you

WPaffhausen

Hello Kafka89,

Change line 02 to be since the returned object column names are key:

sqlQuery = "SELECT SUM(TDATASEG_T.AMOUNT) as AMOUNT FROM TDATASEG_T WHERE TDATASEG_T.LOADID = ? "

Change line 06 to be:

while rs.next():

  var1 = rs.getString("AMOUNT")

  fdmAPI.logInfo(str(var1))

rs.close

rs = None

JohnGoodwin

Another way, you can reference columns by the column index so no need to alias, also it depends what you want to return the value as, it might not be string

For example

try:     sqlQuery = "SELECT SUM(TDATASEG_T.AMOUNT) FROM TDATASEG_T WHERE TDATASEG_T.LOADID = ? "     loadId = fdmContext["LOADID"]                 params = [loadId]                 rs = fdmAPI.executeQuery(sqlQuery, params)     while rs.next():        var1 = rs.getDouble(1)    fdmAPI.logInfo(str(var1))     fdmAPI.closeResultSet(rs) 

kafka89

@JohnGoodwin - this gave me 'object has no attribute 'GetDouble'' error.
Basically I`m trying to enrich import log with info for users so they can check if total Balance is zero.

JohnGoodwin

I have tested and it looks to be working

If you are happy with the type being a string for just writing to the log then you could use getString(int) or stick with getString(String)

Alternatively you can convert the type.of your choice

The list of methods available for ResultSet are available in the Java docs.

Select whichever way works best for you

kafka89

I must have made a typo before or sth as it works like magic! Thank you John.