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






Re: aggregate functions: problem with SELECT SUM()

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







Re: aggregate functions: problem with SELECT SUM()

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)








Re: aggregate functions: problem with SELECT SUM()

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")







Re: aggregate functions: problem with SELECT SUM()

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"