How to calculate the total of a single column in hana table

0
votes

Is there a way to get the total sum of a column in hana db table, and use it in sapui5 app via odata or xsjs?

SQL console on hana db enter image description here XSJS Code I am trying

var query = "SELECT SUM(\"Schema_Name\".\"Table_Name\".\"Column_Name\")" + " AS TotalItemsOrdered FROM \"Schema_Name\".\"Table_Name\"";

var conn = $.db.getConnection();
//var pcall = conn.prepareCall(query);
var pcall = conn.prepareStatement(query);
pcall.execute();
var rs = pcall.getResultSet();
var output = {};

//Here you parse rs and put it to output
while (rs.next()) {
    output.total = rs.getString(1);
    output.results.push(output);
}
rs.close();
pcall.close();
conn.commit();
conn.close();
$.response.contentType = "application/json; charset=UTF-8";
$.response.setBody(JSON.stringify(output));
$.response.status = $.net.http.OK;

Still not getting it. SQL statement is working fine in sql console, returning the sum. But not working in XSJS(any idea what's wrong on the code?

1
sapui5hana

1 Answers

0
votes

You parse the result into a string? What for?

Also, what error messe do you receive?

For UI5 programming there are plenty examples in the developer guide. So if your question is that broad, better make sure you read up on the basics there.

BTW: in SQL you typically only make the full reference of tables in the FROM clause and use table aliases from thereon:

SELECT 
      SUM(tab1."Column_Name") AS "TotalItemsOrdered" 
FROM 
     "Schema_Name"."Table_Name" tab1;

And when you want to have your CamelCase-style column naming be preserved then you need to put it in double quotes.