aggregate functions: problem with SELECT SUM()

Maximus


Hi there,
I wonder why the function below returns 0 (it should return 4335)
* the conn to the db is ok (this function works with diff parameters, it has somthing to do with the SUM)
* I check the SQL code on the db - and the return result is not 0

Code Snippet

Function test3()

Dim objDB, arrRecord, strRecord, strOutput
Dim oRS, nRec, oFld
Dim row

Set objDB = DBConnect()
Set oRS = objDB.Execute("SELECT sum (emp_AMOUNT) FROM production WHERE id=2513")

nRec = 0
row = 1
Do While Not oRS.EOF
For Each oFld In oRS.Fields

test3= oFld.Value
row = row + 1

Next
oRS.MoveNext
Loop

End Function

Shasur

Try using the schema owner

replace

"SELECT sum (emp_AMOUNT) FROM production WHERE id=2513")

with

"SELECT sum (emp_AMOUNT) FROM "DBA".production WHERE id=2513")

usually your query works fine in DB But you need to specify the 'owner' when it comes to Recordset Source

Cheers

Shasur

Maximus

thank you for your help Shasur.

I'm not sure. When you wrote that I should add DBA, do you mean the word DBA or what it represents which is "perfectDB"

Code Snippet

Function DBConnect()
Set objDB = CreateObject("ADODB.Connection")
objDB.Open "perfectDB"
Set DBConnect = objDB
End Function

also, I read on the web, but could implement successfully, that for aggregation I should use:
OpenRecordSet instead of execute ( not sure)

Shasur

You should do that in the query

replace

"SELECT sum (emp_AMOUNT) FROM production WHERE id=2513")

with

"SELECT sum (emp_AMOUNT) FROM "DBA".production WHERE id=2513")

Cringing Dragon

Firstly - in your original example there's a space between the word sum and the bracket - is that supposed to be there I don't normally use a space between a function and it's arguments, but then I don't know whether that matters in whatever version of sql you're using.

In Shasur's suggested solution, assuming that DBA is the name of a variable containing the database name, then between the quotes and the variable name you'll also need concatenation characters (&) to keep the text string intact. Eg

"Select sum(emp_AMOUNT) FROM " & DBA & ".production WHERE id=2513"

Otherwise, if DBA is simply the name of the database, and not a variable, then don't enclose it in quotes. Eg

"Select sum(emp_AMOUNT) FROM DBA.production WHERE id=2513"