TehcKnowledge Content
Issue
Advanced ODBC query to roll up like distributions into just one record and excludes any distributions that roll up with a $0.00 total.
Resolution
This can be accomplished by following these steps:
1. Create the header Query as normal.
2. Go to: Start | Settings | Control Panel | ODBC.
3. Click Add.
4. Choose the Microsoft Text Driver and click Finish.
5. Give the datasource a name and description.
6. Unmark the Use Current Directory checkbox and click the Select Directory button.
7. Path out to the directory where your source file is located.
8. Click OK to save the datasource. The datasource should be seen in the list in the ODBC Datasource Administrator window.
9. In Integration Manager, create a new Query.
10. The Query will be an Advanced ODBC Query.
11. Give the Query a name and description.
12. Select the datasource that you just created from the Data Source drop-down.
13. Type in the following SELECT statement (the column names and source file reference will need to be changed to reflect what yours actually are):
Select * from (SELECT SUM (Amount) As SumAmt, [Document Number], Customer, Account, Type from rmdist.txt GROUP BY [Document Number], Customer, Type, Account ORDER BY [Document Number], Customer, Type, Account) Where SumAmt <> 0
*The field Amount is the debit or credit amount that you want to sum.
*You will need to use the As statement to give this summarized field a name such as SumAmt that will be used in the final Where clause with a <> 0 criteria.
*Any fields containing a space in the name will need to be enclosed in brackets such as [Document Number].
*You will need to select all the fields that you would like to map in the destination mapping window.
*The Group By and Order By statements should include ALL fields selected, EXCEPT for the Amount field.
*Be sure to preview the query before continuing. The accounts should be rolled up properly in the query.
14. Finish setting up the integration and run it.
This article was TechKnowledge Document ID: 27956
