I recently received an interesting question from a customer who is converting a Documaker Standard Edition system to Documaker Enterprise Edition: 

I’m looking for information on how to use Sort by Rule for our batches, but the Administrator Guide doesn't contain much information: “The Sort By rule supports a table.column name reference with an ascending/descending indicator. You can also sort multiple columns.” and “BCHINGSORTRULE Additional sort criteria to sequence the BCHS_RCPS records the Batcher creates. This value is also passed to the new batch record via BCHS.BCHSORTRULE column.” and “BchSortRule Typically a RCPS column name like RCPS.ADR_POSTALCODE is set to sort a scheduled batch by the postal code. Should be a table qualified name so that other tables are candidates in a multi-table select call, e.g. FROM BCHS,JOBS,TRNS,RCPS. This becomes a ORDER BY clause in the select statement if provided to override any sorting the system does normally. A comma delimited list of columns and ASC and DEC key words can be used to as in a ORDER BY clause.”

What a great question! To start off with, if you don't already have an ODEE system, you can revisit my blog post on setting up a quick VM. The default sort order is by TRN_ID then RCP_ID. More specifically the RCP_ID is order in which recipients for a transaction were generated and processed within the Assembler and Batcher. From my own investigation the TRN_ID appears to be the order in which jobs were received and processed by the DocFactory. You might assume this would be the clock time, however there are a number of factors than can influence this.  For example, work is processed in small groups, controlled by worker "fetch sizes", and the ID values are allocated in batches for faster execution. So if you have a multi-instance DocFactory installation, there could be slight differences in job/transaction ordering due to latencies and processing time. 

In this snapshot of the BCHS_RCPS table (which relates batches and their recipients to jobs and their transactions), you can see that the RCPSEQ ordering does not correlate to JOB_ID ordering — but it does indicate TRN_ID ordering. Note: RCPSEQ is applied at print generation time according to the sort order applied to the batch, so this tells us that the sort ordering, absent any criteria, is TRN_ID.

 
Generally this does not present a problem, since it is a default, and you can override it. Let's review! The documentation tells us that the Sort By Rule is a SQL ORDER BY clause, so that tells us we can use any data that is in a column in the ODEE schema. Since the system has a view of the entire context of a transaction across all the system tables (JOBS, TRNS, BCHS, RCPS, PUBS) we don't have to wire up any complex SQL clauseSince the system has a view of the entire context of a transaction across all the system tables (JOBS, TRNS, BCHS, RCPS, PUBS) we don't have to wire up any complex SQL clauses, we can just reference the columns we need in a comma delimited fashion.
 
Let's assume that my print requirement states that the output batch must be sorted by a key identifier and the recipient's name. The key identifier is contained in the TRNS table and the recipient name is mapped into the recipient ADR record — this is automatically mapped in the RCPS.ADR_NAME column. My reference system is already set up to map and populate these values, so to use them in the SORT BY RULE setting for my scheduled batch I can do the following:
  1. Open Documaker Administrator, select the assembly line, and click Batchings.
  2. Select the batching I want to update, click the Rules tab, and enter my rule:

Sort By Rule

TRNS.KEYID, RCPS.ADR_NAME

I can save the change, and test by running a few transactions that I know will be routed to this batch. Note: for testing, make sure you close any open batches for this batching, otherwise the transactions will go into the open batch and will not create a new batch with the new setting. We can see the batch is created with my sort rule in place:
 
SELECT BCH_ID, BCHNAME, BCHBY, BCHSORTRULE FROM BCHS WHERE BCH_ID=284;
Before the batches closes, I want to take a look at the contents by applying my own sorting in a query — I can compare this to the ordering for print to make sure my sort was properly applied. If we take a look at the contents of the batch, we can see the RCPSEQ is not populated (because the batch hasn’t printed yet). You can also see that I'm using an INNER JOIN to pull the data from the TRNS and RCPS tables. The system will do this automatically, but since I'm going into the database I have to do it myself. I’m applying the same ordering that I expect the print process to apply, so when we generate this print we should see the RCPSEQ ordering match this control ordering:
 
select  BR.BCH_ID,BR.JOB_ID,BR.TRN_ID,BR.RCPSEQ,R.ADR_NAME,T.KEYID
from bchs_rcps  br inner join RCPS R on r.rcp_id = br.rcp_id inner join TRNS T on t.trn_id = br.trn_id
where br.bch_id = 284 order by T.KEYID, R.ADR_NAME;
 
 
I force the batch to close by running another SQL statement to update the batch close time to yesterday, so it will print immediately. After the print is generated, I can do the same query as above, but instead of specifying a sort order by the KEYID and ADR_NAME, I use the RCPSEQ (recall this is the print order). During print, the transaction recipients are ordered according to the sort criteria (in this case KEYID then ADR_NAME) and then the RCPSEQ is applied. We can review the print output to see if it matches — I trust that it does  ?  Note that default ordering is supposed to be ASC according to SQL standard, but I would explicitly state DESC or ASC e.g. "TRNS.KEYID DESC”. In my example above, I did not, and you can easily see it is ASCending.
 
select  BR.BCH_ID,BR.JOB_ID,BR.TRN_ID,BR.RCPSEQ,R.ADR_NAME,T.KEYID
from bchs_rcps  br inner join RCPS R on r.rcp_id = br.rcp_id inner join TRNS T on t.trn_id = br.trn_id
where br.bch_id = 284 order by RCPSEQ;
 
 
So, now that we know we can control the sort ordering, what about some interesting cases? The most likely case is that you'll want to sort based on some values that you pass into DocFactory from your extract data, and perhaps you've mapped those the TRNCUS* fields. If you've looked at the ODEE Schema, you might notice there are RCPCUS* fields as well, so you might be assuming you have to further shove data around… rest assured you do not! In fact, as I mentioned earlier, the transaction/recipient context encompasses the entire schema, so you can reference any of the tables and the relationships are already established. 
 
You can also do other interesting things with SQL here. Let's assume that your KEYID is actually concatenated information that you need to use for further sorting. You can use a substring function to obtain and sort by portions of data. In this query below, I apply an ORDER BY clause that uses SUBSTR() function so I can sort by a portion of the value:
 
select  BR.BCH_ID,BR.JOB_ID,BR.TRN_ID,BR.RCPSEQ,R.ADR_NAME,T.KEYID
from bchs_rcps  br inner join RCPS R on r.rcp_id = br.rcp_id inner join TRNS T on t.trn_id = br.trn_id
where br.bch_id = 284 order by SUBSTR(T.KEYID, 1,2) desc,R.ADR_NAME;
 
 
You would apply this to the batch by using the Sort By Rule:  SUBSTR(TRNS.KEYID, 1, 2) DESC, RCPS.ADR_NAME  Note: the exact syntax depends on your database. Oracle DB uses SUBSTR() and SQL Server uses SUBSTRING(). Happy sorting! If you have any questions/comments/concerns, feel free to leave a comment below.