Using SUM(*) with ResultSet

Using the following code works well:

int i = resultSet.findColumn("MY_COLUMN")

However, what do I use if my SQL call contains a:

"SELECT SUM(MY_COLUMN) FROM ....."?

The first statement no longer locates the column index for MY_COLUMN when I use SUM. How do I find the column for this?

LVL 2
BergJCAsked:
Who is Participating?
 
JodConnect With a Mentor Commented:
There are two simple solutions to your problem.

1)

Use an alias for the summed column name so that it is called something useful. An alias is just a name you can superimpose on a column. For example:

"SELECT SUM(MY_COLUMN) as SUM FROM table"

[you may need to check exactly how your database specifies an alias name for a column]

Now you can identify the column as

int i = resultSet.findColumn("SUM")

This is the preferred way to handle the problem. You can however also do:

2)

Index into the column directly by using

public int getInt(int columnIndex)
           throws SQLException

Gets the value of a column in the current row as a Java int.

Parameters:

columnIndex - the first column is 1, the second is 2, ...

Returns:

the column value; if the value is SQL NULL, the result is 0

Not as good a solution as it require you to know which column the value is in, but will still work.
0
 
mantishCommented:
you can use the prepare statement. that way it will make the execution of the query more effecient .
 
Connection theConnexion = null ;
PreparedStatement theStatement = null ;
String str_col_name = "MY_COLUMN" ;
theStatement = theConnexion.prepareStatement("SELECT SUM(?) FROM .....");
 theStatement.setString(1,str_col_name);
 thestatement.executeQuery();


                                                           mantish
0
 
BergJCAuthor Commented:
The query itself is not what I'm having a problem with. My code above is after the query has already been executed. I have many SQL statements that select different columns. I have only one method that executes these statements and retrieves the data from the resultSet. That's why I need to use the findColumn method. I know all the columns in the table, but I don't know if they were in the SQL call, so I look for them all.

My problem comes when I try to select the SUM of a column. The findColumn method for that column name no longer "finds" it. I need to know how to use findColumn with a SUM...
0
 
Ravindra76Commented:
Hi BergJc,

  In my view, when you are locating the column, the reuslt set is becoming in valid or crosiing the your column. Just post the code and the exceptionStackTrace() you encountered

Best of luck
0
 
siva_gunturuCommented:
hi berqjc

try this one

int i= resultSet.findColumn("SUM(MY_COLUMN)");


:-)
0
All Courses

From novice to tech pro — start learning today.