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.ClientKeyORDER 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.