Have you ever been working with a data transformation in which you needed access to database tables that are not part of the source or target data of the transformation? If so, you are not alone, a large portion of the EXTOL customers that I work with have been facing this challenge.
The answer to your problem is EBI’s SQL Access type actions. This functionality allows for the execution of an arbitrary SQL statement over any database accessible via a JDBC driver. Users can now execute any SQL statement that they wish from within EBI rulesets and also have the capability to provide as many input and output parameters as they wish. Performing a lookup over a database table that is outside of the source or target metadata within the ruleset in order to retrieve a value based on selection criteria that comes from the source data of the transformation is no longer a challenge.
Don’t yet see a use for SQL Access actions for your daily operations? Keep reading, because there are a variety of uses that you may not be thinking about.
One that I find particularly interesting is the idea of using SQL Access for data validation. Perhaps you need to validate item numbers within source business documents against your product line item numbers, which you maintain in a database table. Do you really want to maintain them again within a code table in EBI when you have so many and they change so often? Instead, pass the item number from the source data to a SQL Access action that performs a SELECT interaction using the item number as the WHERE criteria. If the SQL Access returns a null value, you have an invalid item number.
OK, enough with the SQL SELECT talk. You can also perform INSERT, UPDATE, and DELETE interactions. Did you ever think about using an INSERT interaction to create an internal log table for your users that tracks something like purchase order numbers or even data from the enveloping segments of EDI documents? What about performing an UPDATE interaction over a database record right in the middle of a transformation?
And then there’s the SQL interaction that scares everyone…DELETE. A solid testing effort on your part will relieve those feelings potentially making the DELETE interaction a very useful tool in your daily business. But proceed with caution because once it’s gone, it’s gone!
The potential use of SQL Access actions is endless and ever-changing with the creativity of our customers. Hopefully you’ll start thinking of how this functionality can make your integration easier.