StrataFrame Forum

Using a Group By statement when filling a data table

http://forum.strataframe.net/Topic14891.aspx

By Andria Jensen - 3/14/2008

I'm using a sql statement like the following:

SELECT CheckDtl.AcctNo, Transactions.ClientKey as ClientKey,  
SUM(CASE WHEN CheckDtl.Amt > 0 THEN CheckDtl.Amt END) AS CreditAmt,
SUM(CASE WHEN CheckDtl.Amt < 0 THEN ABS(CheckDtl.Amt) END) AS DebitAmt
FROM CheckDtl 
INNER JOIN CheckHdr ON CheckHdr.CheckHdrKey=CheckDtl.CheckHdrKey 
INNER JOIN Transactions ON Transactions.TransKey=CheckHdr.TransKey 
WHERE CheckHdr.CheckType<>5  AND CheckHdr.CheckDate >= '01-Jul-2006'
  AND CheckHdr.CheckDate <= '31-Jul-2006'
GROUP BY CheckDtl.AcctNo, Transactions.ClientKey
ORDER BY CheckDtl.AcctNo, Transactions.ClientKey

Now this query returns exactly what I want when run against the database, but when I try to use it in a FillDataTable method I get the following error:

System.ArgumentException - Column 'CheckHdrKey' does not belong to table CheckDtl.

Now, is it still looking for the primary and foreign keys to be in the query even when I'm not using them?  I can't figure out a way to put them in the query and produce what I want with the grouping the way I have it.  Please give me some input here, or let me know if I'm doing something wrong.  Thanks.
By Trent L. Taylor - 3/14/2008

Well, let me tell you this first, double check your query.  If the query works within SQL Server Management Studio query then it will not be affected through the FillDataTable.  I will tell you a little story here...the other day I was getting one value when executing a query within SQL Server Management Studio and another when I called ExecuteScalar on the BO....I would have SWORN there was an issue...I kept looking over my command, debugged the ES Server, even got another pair of eyes to look at my query.  Over an hour later...um...Ben finally saw what was wrong...I left of an @ in front of one of my parameters. Blush

So it happens to us all...since I do not have your data structures it is hard for me to tell you what is exactly wrong with your query.  But I would double-check what you are doing there.

I am sure that the query that you posted was just to show me some values, but you should definitely be using parameters in your actual query command versus embedded values.

By Andria Jensen - 3/14/2008

Ok, you were right...sort of.  Haha.

My SQL was fine, the query worked correctly.  But, I had something in the Navigated event that was using a property that wasn't returned by that query in that specific case.  It confused me because the error wasn't breaking into the event...it was breaking into the FillDataTable method.  Either way, it's fixed now, so thanks for the point in the right direction.

By Trent L. Taylor - 3/14/2008

No problem...I figured it was something along those lines.  Glad you found it Smile