Query Builder Help

ZeroFactorial · November 11th, 2009, 12:33 PM

Vince,

Thanks for the reply. I was able to capture the error and it ispointing to the Select Distinct not having a Group By.

Error:

Code:

{"Column 'AEHospital.REGION' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."}

Where would the Group By portion go?

ZeroFactorial · November 11th, 2009, 12:39 PM

Vince,

I was able to correct this. I just added the Group By at the end of the FromClause method and it worked perfectly.

Thanks for taking the time to help me track this down.

ZeroFactorial · November 11th, 2009, 01:51 PM

One last favor Vince and I should be good to go.
Is there a way to see how the Query String is put together prior to being passed?

I have one Query Parameter and if I select it and add it for the report I am catching this error.

Code:

{"Incorrect syntax near the keyword 'WHERE'."}

I would like to see what the combined SQL query looks like. Is it possible to do that?

The whereClause being passed contains this string

Code:

" WHERE convert(varchar, AETransfer.RequestDate, 101) = '10/09/2009'"

varallov · November 11th, 2009, 01:56 PM

The Where has to go before the Group By Clause.

ZeroFactorial · November 11th, 2009, 02:02 PM

Ok right, this goes back to where should the Group By be placed then.
Doesnt seem like the Framework handles this. I will extend the ENTBaseQuery and see if I can get it to work.

I take it this should follow the pattern of the Select and From Methods.

Thanks

varallov · November 11th, 2009, 02:07 PM

Yes, you could take this approach.You could add a GroupBy virtual method in the base class and then override it in your class. The Select method would also need to append the GroupBy clause after the where clause.

ZeroFactorial · November 11th, 2009, 04:17 PM

Thanks again Vince.

For anyone that ever needs this, I added this functionality to the Framework as it may be needed again.
Below you can find what I did to get this working.

ENTBaseQueryData
Added abstract method GroupByClause
Changed Run method to include GroupByClause

Code:


return db.ExecuteQuery<T>(SelectClause() + FromClause() + whereClause + GroupByClause(), newObject[] { }).ToList();

Implement the GroupByClause in your QueryData class and it should work.