Forum Stats

  • 3,851,857 Users
  • 2,264,045 Discussions
  • 7,904,880 Comments

Discussions

fdmAPI.executeQuery - return Select SUM

kafka89
kafka89 Member Posts: 13
edited Sep 28, 2019 8:04AM in Financial Data Management

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

Best Answer

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Sep 28, 2019 7:51AM Answer ✓

    I have tested and it looks to be working

    pastedImage_0.png

    pastedImage_1.png

    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

    pastedImage_2.png

    pastedImage_5.png

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

    Select whichever way works best for you

Answers

  • WPaffhausen
    WPaffhausen Member Posts: 412
    edited Sep 27, 2019 9:20AM

    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

    kafka89
  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Sep 27, 2019 9:29AM

    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
    kafka89 Member Posts: 13
    edited Sep 28, 2019 6:06AM

    @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
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Sep 28, 2019 7:51AM Answer ✓

    I have tested and it looks to be working

    pastedImage_0.png

    pastedImage_1.png

    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

    pastedImage_2.png

    pastedImage_5.png

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

    Select whichever way works best for you

  • kafka89
    kafka89 Member Posts: 13
    edited Sep 28, 2019 8:04AM

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