Stonefield Query
 
Stonefield Site Map
Stonefield Support

  Overview
  Submit Support Ticket
  Forums
  Online Help Files
  Services
  Pre-Sales Support
Online Webinars
Video Demonstrations
Feature Requests
  Software Maintenance
  Support Testimonials
  FAQ's
Quick Links
Buy Now!
Download Free Trial
Watch a Video
Contact Us

 
 You are here: Support * Support Forums   Search

 Stonefield Query Support Forum

Welcome to the Stonefield Query Support Forum. Please note that in order to participate in the support forums, registration at our site is required. If you are already a member of our site, please sign-in.

 
Forums > Stonefield Query SDK > General Support
Subject: Using Max, Avg, Sum functions

You are not authorized to post a reply.   

Author Messages
Eyejay
Posts:19
06/29/2008 11:24 AM Alert 

I'm using your query tool as part of a package we bought from another company. I've been trying to modify the SQL statement in the Advanced pages but I can't seem to get my SQL statement to work. I'm looking to get the Max, Avg and Sum functions working but end up with a Var2 missing error message (I can't remember the exact message, dev machine is in work). An example of my SQL string is:

select Table1.Var1, max(Table2.Var2) from Table1 inner join Table2 on Table1.Var1 = Table2.Var1 group by Table1.Var1 order by Table1.Var1

This string works for me in SQL Server 2005 but gives me the error in your query tool. If I remove the max() function and the group by condition then the query runs. The problem is the same whether I use max(), avg() or sum().

Is there a way around this or am I doing something wrong?

Thanks in advance.
Chris Wolf
Posts:217
06/30/2008 2:46 PM Alert 

If the Var2 field is on your report then you must ensure that that field is included in the SQL statement.


You can use your SQL statement as is if you remove the Var2 field from the report.
- Chris Wolf
Stonefield Software Inc.
Eyejay
Posts:19
06/30/2008 3:47 PM Alert 
Hi Chris,
Thanks for your reply. I can confirm that the Var2 field is included in the report. I'm not sure if your second comment contradicts your first or have I misunderstood? The only thing I'm thinking now is that in my SQL Server db I use max(Var2) as 'Max Var2'. Using the 'as' component doesn't seem to work in your query tool, as best I can see, although I may be wrong and just don't know how to do it? When you say Var2 must be on the report do you mean Var2 itself or the 'as' equivalent, if the latter how do I do that?

Many thanks, Ian.
Chris Wolf
Posts:217
06/30/2008 4:37 PM Alert 
The reason the error is coming up is because Var2 is on your report (in step 2 of the report wizard) and so Stonefield Query is expecting there to be a field called Var2 in the data set that comes back from the database.

However since your SQL statement is selecting max(Table2.Var2) this field will not be called Var2 in the final data set. You can get around this by using an AS clause to rename max(Table2.Var2) to Var2.

Try:

select Table1.Var1, max(Table2.Var2) as Var2 from Table1 inner join Table2 on Table1.Var1 = Table2.Var1 group by Table1.Var1 order by Table1.Var1
- Chris Wolf
Stonefield Software Inc.
Eyejay
Posts:19
06/30/2008 4:53 PM Alert 
That makes sense, I'm back in work tomorrow (UK time) and will check it out.

Thanks again, Ian.
Eyejay
Posts:19
07/01/2008 8:17 AM Alert 
Hi Chris,

That worked fine, the code ran and generated the report. Of course as the standard report had no field for the 'as' variable I had to edit the Advanced layout and insert a new Field on the report with the name of the 'as' variable, but no problem.

Thanks for your help, Ian.
You are not authorized to post a reply.
Forums > Stonefield Query SDK > General Support > Using Max, Avg, Sum functions


ActiveForums 3.6

 
 
   Privacy Statement  |  Terms Of Use
© 1996-2008 Stonefield Software Inc. All rights reserved.