REVMEDIA FKB Articles - AREV, OI, REV G, jREV, JCDC, LIVEWATER,ADVANCED REVELATION, OPENINSIGHT, REVELATION G

RevMedia FKB

Document	V1I7A2
Title	V119 - Part I
Keywords	EXTERNAL.SORT V119 SORT
Text	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 @RECORD<11 X> = DAT 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 IN OLD USING @VM SETTING X THEN @RECORD<10 X> = ARR @RECORD<11 X> = AMOUNTS<0 Z> END NEXT END (Volume 1 Issue 7 Pages 4 9 10)

[revmedia/copyrigh.htm]

Page last modified: 08/02/03