Reject Sub-System

by Paige Roberts, Sr. Technical Content Developer, Pervasive Software, Inc.

The reject sub-system is a handy bit of Pervasive functionality that frequently gets overlooked. Essentially, it allows a file to be split into two targets, with the second target, the reject, being of the same type as either the source or the main target. Any criteria that can be specified in a RIFL script can be used to split the files. This functionality isn't anywhere near as advanced as the multi-column complex data profiling criteria that the the Pervasive Data Profiler can use to split files, but it can serve a similar, if less sophisticated function.

The Reject function is frequently confused with the Discard function. Discard simply throws away records that don't meet a certain standard that you specify in RIFL script. Discard preserves those records exactly as they appear in your source, and places them in a different file or table or even a different database from the Target, so that you can then re-process them in a later automated process step, or look at them with human eyes to determine what needs to be done with them.

Being able to separate records based on any single, or even on multiple criteria is surprisingly useful. You can for instance, process all records in a file that have a positive balance, and place all records with a negative balance in a different file. You can process all records that have a valid date later than Jan 1, 2000 and place the rest in a different table in your database. You can load every record coming out of your Sybase database into your Oracle database for clients with residences inside the country who have made a purchase in the last five years, and place the rest in a delimited text file. There are thousands of possible uses.

Here's how it works:

1. Set up your map to go from any source to any target. I took the easy way out in my example and used the Tutor1.asc example file provided with the software installation as my source. My target is just another delimited file. This should make it a simple matter for you to reproduce my example map. Define a criteria, and in the source AfterEveryRecord event handler or in a field expression, put in a conditional statement for the criteria like:

This will reject any customers in our example source data who are from the state of Texas.

2. Put a standard ClearMapPut action in the source AfterEveryRecord event handler that points to the regular Target file or table, assuming that one hasn't already been added for you automatically. If it has, don't add another one unless you want two of every record in your target file. This is how the AfterEveryRecord handler should look:

3. Specify a connection string to the reject file. To do that, in Transformation and Map Properties, click the Reject Connect Info link in the left tree. Click the Build Connection String from Source, or if you'd rather have the reject file of the same type as your target, then click Build Connection String from Target. If you would like the Reject file to be a completely different kind of data from the source or target, then click Build New Connection String, and connect just like on the standard source and target tabs.

If you opted to build your reject connection string from your source or target connections, then click the three dots to open that string. Change the file or table name, so that it isn't the same as your target or source. You can also adjust any properties you want here. Only properties that are different from the default are really required. You can delete the rest if you'd like to make it clearer. In my example, I just set the header property to true and changed the file name to be different from the target file, and deleted the rest of the properties for clarity.

4. Put a ClearMapPut in the target OnReject event handler that goes to the Reject file or table.

That's it. Run your map, and you will get two outputs - in this case, two comma delimited ASCII files. In the target file, you'll find 98 customers from Arkansas. In the reject file, there will be only the two records with Bobbi Arndt, and Jeff Holland, the two unwanted people from the state of Texas.

One thing to remember is that target field expressions which transform the data will not be executed on rejected records. Records in the reject file will be exactly the same data as in the source, even if you output them to a different target format.