A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Monday, March 1, 2010

GLAcct Table Gotcha

Assumptions are the bane of any query project, and one assumption I recently made bit me today. I had assumed that only Income and Expense type accounts would have any value stored for the ClosingAcctNu column in the GLAcct table. After all, only those two types of accounts use a closing account. In the software they are the only kinds of accounts that allow you to see or to enter any value for closing account information.

I was wrong. Somehow the ClosingAcctNu column in a customer's data was populated with account values for header accounts, total line accounts, and possibly others as well. This was not a conversion issue; these were brand new accounts in a brand new chart of accounts. I had to adjust my query for this customer in order to specifically restrict the search for closing accounts to the detail account type, in order to avoid picking up erroneous closing account information from the Header and Total Line rows in the GLAcct table.

This was particularly frustrating because it only became apparent in the "real world" environment; in my test database there are no extraneous closing account values in the GLAcct table.

Whenever you get unexpected results, looking for the culprit can be tricky. Just be prepared for the unexpected to sometimes be lurking even in a query of familiar tables.

No comments:

Post a Comment