CoSORT: Exclusive Single-Pass Join and Reformatting Technology

CoSORT is the only sort package with fully integrated join and reformatting capabilities. More than four years ahead of the market and up to 20 times faster than a database, CoSORT's sort control language (SortCL) features a straightforward, SQL-based join command that matches two pre-sorted files at a time at I/O speed!

CoSORT's SortCL uses SQL inner and outer join syntax, to produce a combined output based on specified conditions. Use additional SortCL scripts to join three or more tables. You can even eliminate inner join results from an outer join (using the ONLY attribute), and eliminate and reformat null records. Again -- and only with SortCL -- you can also continue to manipulate the resulting data in the same join script and I/O pass.

The /JOIN statement takes the form:

/JOIN [type [ONLY]] left right [WHERE condition]

where the join types are:

and the WHERE condition can be used to qualify or limit the join action.

SortCL's join creates an output stream of paired records from the left input stream (file, pipe or procedure) and the right stream. The INNER join pairs satisfy the condition. OUTER joins show both matches and records (from the left or right) for which there is no match in the other input file that satisfies the condition; blanks or nulls are inserted for those non-matches fields. In an UNORDERED join, every combination from the left and right are produced.

By way of example, consider these two, pre-sorted input files. First, the archive file 'customers'

11010	Smith, John	555-1111
12212	Stone, Allan	555-5678
12214	Ross, Margaret	555-4321
12332	Smithson, Mary	555-5847
12345	Jones, Thomas	555-0098
13322	Ellis, Wayne	555-9375
13332	Beck, Tim	555-2298
13333	Wright, Bill	555-1029
13342	Hartman, Jill	555-2376
13456	Milton, Sam	555-3434

and then the transaction file 'purchases'

Nails	Hardware	10102
Skis	Sporting	12212
Shoes	Footwear	12345
Football	Sporting	13322
Whiskey	Alcohol	13332
Boots	Footwear	13342
Hammer	Hardware	13456
Wine	Alcohol	23373

The SortCL job specification file, 'join1.scl', lays out and joins the inputs:

/INFILE=customers /FIELD=(Acct_nb,POS=1,SIZE=5) /FIELD=(Name,POS=9,SIZE=18) /FIELD=(Phone,POS=27,SIZE=8)/INFILE=purchases /FIELD=(Item,POS=1,SIZE=10) /FIELD=(Category,POS=11,SIZE=11) /FIELD=(Acct_nb,POS=22,SIZE=5) /JOIN INNER customers purchases WHERE \ customers.Acct_nb == purchases.Acct_nb /OUTFILE=stdout /FIELD=(Name,POS=1,SIZE=14,ASCII) /FIELD=(Phone,POS=15,SIZE=9) /FIELD=(purchases.Acct_nb,POS=24,SIZE=6) /FIELD=(customers.Acct_nb,POS=30,SIZE=6) /FIELD=(Item,POS=36,SIZE=9) /FIELD=(Category,POS=45,SIZE=11)

This inner join, in file.field (SQL) syntax, sends the matching records to the screen:

Stone, Allan	555-5678	12212	12212	Skis	Sporting
Jones, Thomas	555-0098	12345	12345	Shoes	Footwear
Ellis, Wayne	555-9375	13322	13322	Football	Sporting
Beck, Tim	555-2298	13332	13332	Whiskey	Alcohol
Hartman, Jill	555-2376	13342	13342	Boots	Footwear

To join additional files simulataneously, this first set of joined output would be piped into another sort (on the next join key), which in turn pipes into the next join script, and so on.