sql mathematic statements

[crymedry]

Hello All:

I have a question and will welcome any help. I have a database that has a list of accounts and values in those accounts. we judge our different sites on this dollar value. I am passing a value from one page to the next, which works fine, but I get an error on my SELECT statement. It is as follows:

GPA = "SELECT *, SUM(CLOSE $) as totclose, SUM(OPEN RES) as totopen, SUM(ADJ FEE) as totadj WHERE OFFICE='"& location&"'"

the location varible is the one passed from the previous page. I get an error on the first SUM statement.

Thank you for any help!

Kris

 

[DotNetGnat]

try this:

GPA = "SELECT *, SUM([CLOSE $]) as totclose, SUM([OPEN RES]) as totopen, SUM([ADJ FEE]) as totadj WHERE OFFICE='"& location&"'"

-DNG

 

[crymedry]

Ok ... I tried that, and now I am getting a:

Syntax error (missing operator) in query expression 'WHERE OFFICE='CB''

the post data is being carried over ... should I do multiple sql statements? Is the fact that the WHERE comes after the SUM statements?

Thanks again for the help.

 

[gmmastros]

From Table ???

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Your next error will problably mention something about aggregates without a group by....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Turkbear]

Hi,
Check the
WHERE OFFICE='"& location&"'" part..it looks like it is not being concatenated correctly..
Try:
WHERE OFFICE='" & location & "'"

( Really shouldn't matter about the spaces, but...)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DotNetGnat]

no...spaces shouldnt be a problem Turkbear...i am thinking may be he needs to do use a replace() function to replace any single quotes...and also has George pointed out...there is no FROM clause in the query...

-DNG

 

[crymedry]

OK .... I realized that there is not a FROM statement in there. I have changed it to:

GPA = "SELECT SUM([CLOSE $]) as totclose, SUM([OPEN RES]) as totopen, SUM([ADJ FEE]) as totadj FROM MASTER WHERE OFFICE='"& location &"'"

Now I get the error that:

The SELECT staement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I have looked and all my parentheses are closed and all quotes accounted for. What am I missing now?

THanks!!

 

[DotNetGnat]

try this:

GPA = "SELECT SUM([CLOSE $]) as totclose, SUM([OPEN RES]) as totopen, SUM([ADJ FEE]) as totadj FROM [MASTER] WHERE OFFICE='"& location &"'"

dont you want to do a group by office?
-DNG

 

[crymedry]

Well I tried that and I get the same error.

I don't want to group by the office cause I'm trying to get the totals for each specific office. The first page of this they select what office to view, and then this page is supposed to pull all the data together and add all of it up.

any more ideas anyone?

 

[Tarwn]

Well, only one item left, might as well put some square brackets around the OFFICE field name also.
If it still complains then it is probably an issue with the $, you can test this by just taking he first sum out of your query and seeing if it returns a result.

I don't think you should get an aggregate error on this unless you put OFFICE in the SELECT portion of your query.

 

[crymedry]

I tried cutting that out, I also tried putting the OFFICE in square brackets, same error.

The statement now looks like this:

GPA = "SELECT SUM([COSE $]) as totclose, SUM([OPEN RES]) as totopen, SUM([ADJ FEE]) as totadj, FROM [MASTER} WHERE [OFFICE]='"& location &"'"

The error that I get is:
The SELECT staement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I am at a loss, any help would be greatly appreciated!!!

 

[gmmastros]

Remove the comma after as totadj

You have a right curly bracket after MASTER. It should be a right square bracket.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[crymedry]

K ... fixed the stupid mistakes .... now I have a "Too few parameters. Expected 1." error.

Here is what I have:
GPA = "SELECT SUM([CLOSE $]) as totclose, SUM([OPEN RES]) as totopen, SUM([ADJ FEE]) as totadj FROM [MASTE] WHERE [OFFICE]='"& location &"'"

Thanks again for the help!!

 

[gmmastros]

[MASTE] ???

I thought it was [MASTE[!]R[/!]]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[crymedry]

Sorry, that was a typo from me just typing it out and not cut and paste, it is [MASTER].

Do you see any operators or punctuation missing?

 

[crymedry]

Is there anyway I can use a response.write statement somewhere to debug this script?

Thanks again!

 

[DotNetGnat]

yes before executing your query...you can do...

Response.write GPA

-DNG

 

[onpnt]

It's possible a large amount of time would have been saved by reading
Development practices when using SQL/Interfaces faq333-4896
and in the future using the methods

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}