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
#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
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.
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.
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
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.
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.