Sort API

DSI SORT is a set of precompiler directives that simplifies the generation of database sort code within a Basic program. Selected records may be sorted by any fields or functions of fields within the database in either ascending or descending order. After a sort has completed, each record is accessed by RFA address for individual processing.

Sort Elements/Syntax

$DSI    SORT      {DATABASE-PREFIX}
$DSI    SEQUENCE  {} 
$DSI    CHANNEL   {DATABASE-CHANNEL} 
$DSI    DESCRIBE  {DESCRIBE-FILE} 
$DSI    BY        {FIELD-NAME}   {DESCENDING} 
$DSI    BY        {FIELD-NAME}   {/TYPE=STRING/LEN=20} 
$DSI    KEY       {KEY-TO-READ-BY} 
$DSI    CHECK     {FIELD-TO-CHECK-FOR-LOW/HIGH} 
$DSI    LOW       {LOW-KEY-VALUE} 
$DSI    HIGH      {HIGH-KEY-VALUE} 
$DSI    LOCK 
$DSI    WORK_ACCESS 
$DSI    SELECT 
$DSI    STATISTICS

    ! SELECTION STATEMENTS
    DSI$_RECORD_SELECTED = TRUE   IF {SELECTION CRITERIA}

$DSI    END SORT
$DSI    START GET

        ! PROCESSING FOR EACH RECORD

$DSI    END GET

SORT [DATABASE-PREFIX]

The database prefix that will be used with the field names. If the prefix ends in a double quote then the field names will be generated using the Basic record format. Otherwise an underscore will be added to the prefix and the result used in front of the field name. For example if the prefix is CL:: and the field name is COMPANY_NAME then the resulting field name will be CL::COMPANY_NAME. If the prefix is just CL then the same field results in CL_COMPANY_NAME.

SEQUENCE [{1}]

The sort sequence number. Each sort in a program must have unique sequence number otherwise the generated code will contain duplicate labels.

CHANNEL [DATABASE-CHANNEL]

The I/O channel on which the database was opened.

DESCRIBE [FILE-NAME]

The database describe file name.

BY [FIELD-NAME] [/DESCENDING] [/TYPE=}] [/LEN={length}]

Field name to sort the records. Records may be sorted in descending order by including the /DESCENDING switch. If the /TYPE switch is used then the sort will use the specified field name without modification. This allows arbitrary data or functions to be used to sort databases. The function can perform any Basic operation including key lookups on other files as well as calling sub programs. If the type used is STRING the the /LEN must be used to specify the maximum length of the string.

KEY [KEY-TO-READ-BY]

Key number to read by for selection based on key value. If this entry is omitted then the database will be read by primary key.

CHECK [FIELD-TO-CHECK-FOR-LOW/HIGH]

This tells the precompiler what field to check using the low and high values in the next two entries to limit the selection. The generated code will use this as a key to limit the number of records read in selecting records.

LOW [LOW-KEY-VALUE]

Lowest Key value to include in sort selection.

HIGH [HIGH-KEY-VALUE]

Highest Key value to include in sort selection.

LOCK

When included, records will be locked when accessed for individual processing.

WORK_ACCESS

If WORK_ACCESS is specified and /TYPE was used in the sort fields to sort on a local variable, the local variable will be reloaded with its value from the sort temp file.

SELECT

Needed to trigger the generation of the sort code.

STATISTICS

Generates code to show records read, records selected and timing.

SELECTION_STATEMENTS

Additional criteria for record selection may be included at this point. Again, any valid Basic code is allowed here. Each record read will be processed here to determine if it is to be selected for the sort. Note that totals can be computed here even though the records are not yet in sort order so values like percentages can be computed in the START GET section without having to make a second pass on the sorted records.

DSI$_RECORD_SELECTED

Set this variable to true for each record to be included in the sort.

END SORT

End of sort loop.

START GET

Beginning of GET loop. Each selected record is accessed by RFA in sort order for individual processing.

END GET

End of GET loop.

Example:

    $DSI    SORT    DATA::
    $DSI    CHANNEL 1
    $DSI    BY      CITY
    $DSI    BY      LAST_NAME
    $DSI    KEY     KEY1
    $DSI    CHECK   DATE_LAST_CONTACTED
    $DSI    LOW     '20080101'
    $DSI    HIGH    '20080615'
    $DSI    SELECT
    $DSI    STATISTICS

    DSI$_RECORD_SELECTED = TRUE   IF DATA::CLIENT_REQUEST_CONTACT='Y'

    $DSI END SORT

    $DSI START GET 

    GOSUB REPORT HEADER
    GOSUB REPORT DETAIL

    $DSI END GET