V119 - Part I

When fields are not BTREE indexed on AREV the system must go through the long process if physically sorting records if a sorted report is requested. Those of you who leave the status line enabled will be familiar with the "% sorted" message whilst the process occurs. The mechanism to achieve this functionality is a program called EXTERNAL.SORT and I set out to document this. However it became apparent in the course of researches that EXTERNAL.SORT was horrendously complex to use BUT relied upon another routine to do most of the hard work for it, a routine called V119. This is an assembler routine and could best be described as "the SORT routine". As we don't wish to replace EXTERNAL.SORT I therefore opted to document V119 instead.

V119 is a multifunction routine designed both to sort data and to handle sort-file management. As these are two discrete areas this discussion will be split over two issues with this issue dealing with using V119 to sort blocks of data that can be fitted into one AREV variable. The first thing to be said for V119 is that it is FAST ! On first experimentation I thought that V119 wasn't actually sorting it sorted so fast. I was acheiving timings of 0.2 seconds to sort hundreds of records. Even on an XT performance is acceptable.

The calling syntax for V119 is

       CALL V119("S", "", ORDER, JUST, DATA, FLAG)

where

    ORDER    is the sort order, A for Ascending, D for Descending
    JUST     is the justification, L for Left, R for Right
    DATA     is the information to be sorted
    FLAG     is an indication of whether the operation failed or succeeded

Sorting Single Data Arrays When the data to be sorted consists purely of single data elements the calling syntax is very straightforward. Consider the following lines of code designed to display a sorted list of entries on the VOC file.

  OPEN "VOC" TO VF THEN
     DAT = ""
     SELECT VF
     EOF = 0
     LOOP
        READNEXT ID ELSE EOF = 1
     UNTIL EOF DO
        DAT := ID : @RM
     REPEAT
     CALL DOSTIME(START)
     CALL V119("S", "", "A", "L", DAT, FLAG)
     CALL DOSTIME(FINISH)
     CONVERT @RM TO @FM IN DAT
     DATA = "Took " : FINISH - START : @FM : DAT
     CALL MSG(DATA, "", "", "")
  END

Note that the data array to be sorted was built up with @RM (Record Markers - Char(255)) delimiters, note also that the data had a trailing @RM. This is intentional, the sort will fail without it.

There seems to be no significant length at which V119 fails to operate. That is to say, V119 does not sort by the first x characters, rather it sorts by all characters. This was verified using the following section of code

  A = STR(@UPPER.CASE, 1000)
  * This produces a variable of 26000 characters
  B = A
  A := "Z"
  B := "A"
  C = A : @RM : B : @RM
  CALL V119("S", "", "A", "L", C, FLAG)
  CONVERT @RM TO @FM IN C
  C[-1,1] = ""
  PRINT C<1>[-1,1]
  PRINT C<2>[-1,1]

This section of code printed Z followed by A, in other words it had sorted B before A.

Sorting Multiple Data Arrays When the data to be sorted consists of multiple sort fields, the call to V119 has to be amended slightly. Firstly, for each sort field there must be a corresponding ORDER and JUSTIFICATION. This is accomplished by creating a string of characters with no delimiters for each passed parameter. Further the data passed should have data records @RM delimited, but sort fields within data records should be @FM delimited.

Thus assuming a requirement to sort a record by four fields, first sort being Ascending Left, second being Descending Right, third being Descending Left and fourth being Ascending Right, the ORDER paramenter would be "ADDA" and the JUSTIFICATION would be "LRLR". Thus modifying the example above to sort by four fields.

  EOF = 0
  LOOP
     READNEXT ID ELSE EOF = 1
  UNTIL EOF DO
     READ REC FROM VF, ID THEN
        DATA := FIELD(REC,@FM,1,4) : @RM
     END
  REPEAT
  CALL V119("S", "", "ADDA", "LRLR", DAT, FLAG)
  CONVERT @FM : @RM TO "-" : @FM IN DAT
  CALL MSG(DAT, "", "", "")

Sorting Single Data Arrays with Associated Values When the data to be sorted consists of a single sort field with associated data fields (EG a date array with corresponding order quantities), the system will cope automatically, just pass the values delimited as above but omit the multiple sort criteria.

  DATES = @RECORD<10>
  AMOUNTS = @RECORD<11>
  CTR = COUNT(DATES, @VM) + (DATES # "")
  IF CTR THEN
     DAT = ""
     *
     * This could also be achieved by imaginative use of the ::: operator
     * and a CONVERT statement
     *
     FOR X = 1 TO CTR
        DAT := DATES<0,X> : @FM: AMOUNTS<0,X> : @RM
     NEXT
     CALL V119("S", "", "A", "R", DAT, FLAG)
     CONVERT @RM : @FM TO @FM : @VM IN DAT
     DAT[-1,1] = ""
     FOR X = 1 TO CTR
        @RECORD<10,X> = DAT<X,1>
        @RECORD<11,X> = DAT<X,2>
     NEXT
  END

This is still a remarkably quick way of sorting, and it keep track of the relationships internally. This type of routine can be used to best advantage on a pre-save process to ensure that records always display associated multivalues in the correct order. (Whether or not associated multi-values ought to be used extensively is a design decision which can be better addressed at a later date.)

Whilst this method is a quick way of achieving an associated single sort, it is worth bearing in mind that under some circumstances quicker results may be obtained by using a call to V119 to sort the controlling field followed by a standard insertion sort. This is of especial interest when the controlling multivalue can be guaranteed to be unique. EG

  DATES = @RECORD<10> ; OLD = DATES
  AMOUNTS = @RECORD<11>
  CTR = COUNT(DATES, @VM) + (DATES # "")
  IF CTR THEN
     CONVERT @VM TO @RM IN DATES
     DATES := @RM
     CALL V119("S", "", "A", "R", DATES, FLAG)
     CONVERT @RM TO @FM IN DATES
     DATES[-1,1] = ""
     DIM ARR(CTR)
     MATPARSE DATES INTO ARR
     FOR X = 1 TO CTR
        LOCATE ARR<X> IN OLD USING @VM SETTING X THEN
           @RECORD<10,X> = ARR<X>
           @RECORD<11,X> = AMOUNTS<0,Z>
        END
     NEXT
  END

(Volume 1, Issue 7, Pages 4,9,10)