Ruby SQL Select Sum 2 Columns?

I have the following definition which is looking at an apache database,
and then a sql database. I need to run a sql statement and sum 2
columns. When I do this, the result that is getting returned is not
even close. It’s not what it should be.

I search the apache database for a list of projects, then for each
project, I search sql for any records that have that project. I want to
sum their project costs column, where the project id’s are equal.

This is the code:

CREATE MAP OF PROJECT IDS IN CURRENT OPEN APACHE CASE

t_Case = $current_case.search(“special-metadata:ProjectID AND
has-exclusion:0”)
t_projectIDs = t_Case.map {|item| item.getspecialMetadata[‘ProjectID’]}
t_projectIDs = t_projectIDs.compact.uniq.sort

connection = java.sql.DriverManager.getConnection(db_url, user_name,
password)

#DEFINITION TO SEARCH THE CURRENT CASE, BY BATCH, FOR ANY DOCUMENTS THAT
HAVE UPDATED TEXT
def select_items(connection, table_oname, tablelog, t_projectIDs)
$t_projectids = Array.new
t_projectIDs.each do |projCheck|
projCosts = $current_case.search(“path-name:#{projCheck} AND
previous-version-docid:* AND has-exclusion:0”)
puts projCheck
dedeupprojCosts = $utilities.item_utility.deduplicate(projCosts)
projdedupCosts = dedeupprojCosts.count
puts projdedupCosts.to_s + " is the unique cost sum"
if projdedupCosts > 0
puts “in projdedupCosts count is greater than 0”
t_projdedupinterest = dedeupprojCosts.map { |pages|
pages.getProperties[“Interest”] == nil ?
pages.getProperties[“InterestVar”] : pages.getProperties[“Interst”] }
t_projdedupinterestSum = t_projdedupinterest.inject(0, :+)
sqlsame = “SELECT sum(projectCost) AS sumProjCost, sum(interest AS
sumInterest FROM " + table_oname + " WHERE AssetTagBatch =
‘#{projCheck}’”
statement = connection.prepareStatement(sqlsame)
rows = statement.execute_query
#IF THERE IS A RESULT FOR THE QUERY THAT THE SQL OCR TABLE HAS A
MATCHING BATCH AND COUNTS, THEN REPORT TO THE CONSOLE AND LOG TABLE THAT
THE BATCH IS FULLY REPORTED
if rows != nil
sqlprojcostsum = sqlsame[0]
sqlinterestsum = sqlsame[1]
puts sqlprojcostsum
puts “that was the sqlprojcostsum”
exit 0
end
end
end
end

select_items(connection, table_projname, table_namelog, t_projectIDs)

If anyone can help, I would appreciate it.

Right now the puts sqlprojcostsum is reporting 83, but for the first
project, it should actually be 1.

Can you put the code in some form of onine pastie or make it a gist?
It’s pretty hard to read. And some empty lines also go a long way in
making this more readable.

Cheers

robert

Sorry, I don’t know what either of those are. Here is a smaller
example, as I am just trying to get ruby to put a sql sum of a column,
based on a select statement.

connection = java.sql.DriverManager.getConnection(db_url, user_name,
password)
sqlsame = "SELECT sum(myfield) AS sumDoc FROM " + table_var
statement = connection.prepareStatement(sqlsame)
rows = statement.execute_query

puts sqlsame
puts statement
puts rows

I tried those puts, to get an output, and none of them give me the sum I
need. The puts sqlsame gives me the sql statement, which I can run in
sql and get the right number (which in this case is 9).

the 2nd two puts give me this:
SQLServerPreparedStatement:7
SQLServerResultSet:7

Which means nothing to me, lol.

Does this make it more simple?

Thanks for responding!

I have looked all over for this issue, and there is so little
documentation that no matter how I search, I can’t find any examples of
what I want to do. It seems this should be simple enough, but no one
can seem to help.

On Mon, Sep 24, 2012 at 9:00 AM, Courtney F. [email protected]
wrote:

I have looked all over for this issue, and there is so little
documentation that no matter how I search, I can’t find any examples of
what I want to do. It seems this should be simple enough, but no one
can seem to help.

Your example:

connection = java.sql.DriverManager.getConnection(db_url, user_name,
password)
sqlsame = "SELECT sum(myfield) AS sumDoc FROM " + table_var
statement = connection.prepareStatement(sqlsame)
rows = statement.execute_query

‘rows’ now contains the results of your query. Do you know what it is?

Hint: in irb, run the above and then try rows.class to find out what
kind of object it is. Then go read the doc for that object.

On Sep 19, 2012, at 1:26 PM, Courtney F. [email protected] wrote:

puts sqlsame
SQLServerResultSet:7

Which means nothing to me, lol.

Does this make it more simple?

Thanks for responding!


Posted via http://www.ruby-forum.com/.

You might look into Sequel or Active Records for SQL. They take quite a
bit of the edge off of DB interaction.

On Mon, Sep 24, 2012 at 9:11 AM, Courtney F. [email protected]
wrote:

When I add

puts rows

I get SQLServerResultSet:9

I am not doing this in irb, it is in the console for the program that is
using it.

irb, console, whatever REPL you’re using is irrelevant.

I think if I get the SQLServerResultSet: part stripped off

Again: the result of your query being executed is not a string, not a
number, it is as it shows above: a SQLServerResultSet .

There is nothing to “strip off”. You need to read the doc for that class
to understand what it is you’re looking at and how to use it.

I think that is my problem. I have the statement executing, but I just
want the result of this sum. Why is this so hard?

When I add puts rows.class
I get

Java::ComMicrosoftSqlserverJdbc::SQLServerResultSet

How does that help me?

When I add

puts rows

I get SQLServerResultSet:9

I am not doing this in irb, it is in the console for the program that is
using it.

I think if I get the SQLServerResultSet: part stripped off, I would be
good, but that would be the total for the whole thing. I am actually in
the middle of a loop and that should be 1 for the first loop. I am
exiting after the first loop, as I am just testing.

Thank you for talking through it with me. I ended up figuring out I
needed

puts rows.getInt(“sumDoc”)

and that gives me what I need. Whew!

On Mon, Sep 24, 2012 at 9:37 AM, Courtney F. [email protected]
wrote:

When I add puts rows.class
I get

Java::ComMicrosoftSqlserverJdbc::SQLServerResultSet

How does that help me?

One More Time: READ THE DOCS FOR THAT CLASS.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs