Tuesday, September 06, 2011
Sometimes custom processes need to be written in order to pull data from legacy ERP System. But many times legacy system have the ability to support ODBC connections making it relatively easy to create simple SQL statements in the outbound file definitions to meet the EDI needs. An additional benefit is that if any data needs to be added in the future, complex coding is not required.
Two areas to pay close attention to when writing SQL statements:
1. Know your data types. This holds true for legacy and non-legacy systems alike. Specifically, date formats and data types are not stored the same way in the database as they appear in the application. For example if a date on the screen of your application is MM-DD-CCYY your application database may actually store the data in the format of CCYYMMDD. The later format is what you will have to use when writing your SQL statement.
2. Find the balance of data between what is in the tables vs. what you need to create the transaction set. SELECT * FROM TABLE is a quick and easy way to ensure all data is pulled. However, it is not the most efficient for your system. By replacing the * (asterisk) with actual field names only the needed data is extract from the legacy and stored in the EDI system.
When used right SQL can improve the workflow of a system. As always, your 1 EDI Source consultants are here to assist you.