Other Variables

As well as ‘normal’ variables described above, there are two other types of variables, scalar variables and list variables.

Scalar variables are variables that have a single value, rather than a value for every object of a particular type, they can be numbers, dates or strings, and are defined using the LET statement e.g.

LET $flag = “XP”

LET $threshold = 123.4

LET $date_threshold = #01-Jan-2003”

As well as setting values, it is possible to set scalar variables to the results of expressions of other scalar variables and constants e.g. LET $diameter = $area / $pi.

As well as these scalar expressions it is possible to set scalars to the results of some queries calculating values for the whole of a network or selection. Scalar variables may be saved to files and read from files, see below.

List variables are used in conjunction with a number of functions, known as ‘list variable functions’.

List variables can either be defined with a set of values, or can be defined with the intention of providing the values later.

To define list variables and to provide a set of values the format of the statements are as follows:

LIST variable_name = list of values (separated by commas) e.g.

LIST $widths = 100, 300, 500, 700, 900

LIST $codes = ‘AB’, ‘AF’, ‘BC’, ‘BD’

The variable name must be valid as described above, beginning with the dollar sign. The equals sign must be there, as must the commas between the individual values. As well as numbers and strings it is possible to have lists of dates, which as described above must begin and end with the # character.

To define list variables with the intention of providing values later the format of the statements is as follows:

LIST variablename

LIST variablename STRING

LIST variablename DATE

e.g.

LIST $mynumberlist

LIST $mystringlist STRING

LIST $mydatelist DATE

Values of a particular sort of query may be stored in list variables, see below.

List variables may be saved to files and read from files, see below.

There are four functions associated with list variables. In all cases the final parameter of the function must be a list variable and only a list variable. In addition, the LEN function may be used to find the length of a list variable. There are no other circumstances in which list variables may be used within SQL expressions.

One of the functions may only be used if the list is sorted, i.e. the values in the list must be strictly increasing, with each value being strictly greater than the previous value. In the case of numbers and dates ‘increasing’ means what you would expect. In the case of strings, it means that the strings are in alphabetical order, however that is defined for the language in which you are running Windows.

LEN

LEN(list variable)

The function LEN may be used to find the length of a list variable, that is to say the number of items in the list. e.g. LEN($widths) with $widths as defined above would return 5.

RINDEX

RINDEX(expression,list variable)

RINDEX is the function that may only be used if the list is sorted. The purpose of the RINDEX function is essentially to divide values into ‘buckets’. It returns 0 if the result of the expression is less than the first value in the list, 1 if it greater than or equal to the first value in the list but less than the second value in the list, 2 if it is greater than or equal to the second value in the list but less than the third value etc.

If there are n items in the list and the result of the expression is greater than or equal to the final item in the list hen RINDEX will return n.

LOOKUP

LOOKUP(expression,list variable)

If there are n items in the list and the expression is between 1 and n inclusive, LOOKUP will return the appropriate item from the list e.g. if the result of the expression is 1 it will return the first item, if it is 2 it will return the 2^nd item, if it is n it will return the n^th and final item. If the value is not an integer or an integer not in the range between 1 and n inclusive, it will return NULL.

MEMBER

MEMBER(expression, list variable)

MEMBER will return the Boolean value true if the result of the expression is one of the values in the list, false otherwise.

INDEX

INDEX(expression, list variable)

If the result of the expression is the first value in the list, INDEX will return 1, if the result is the second value in the list it will return 2 and so on. If the result of the expression is not in the list INDEX returns 0.

AREF

AREF(n,list)

Given a list variable list and a number from 1 to the length of list returns the n^th element in the list e.g.

LIST $badger = 'one','two','three';

AREF(2,$badger) will return 'two'

Obviously this is on some level counterintuitive, you might expect $badger,2, but all the other functions that take list variables take the list as the last parameter so the parameters are in this order for the sake of consistency. This function is likely to be most useful in combination with the looping and IF functionality described later in the paper.

TITLE

TITLE(n,list)

The purpose of the title list function is to provide titles for the 'buckets' when RINDEX is used to partition values into a number of ranges (see above).

Table variables are used in GROUP BY clauses, described later. They are used when the results of the GROUP BY clause are needed in an implicit join, also described later.

Scalar and list variables can be saved to files and read from files as follows:

SAVE $var(,$var,$var...) TO FILE 'filename'

SAVE $var(,$var,$var...) TO FILE $variable

LOAD $var(,$var,$var...) FROM FILE 'filename'

LOAD $var(,$var,$var..) FROM FILE $variable

SAVE ALL TO FILE 'filename'

SAVE ALL TO FILE $variable

The filename may be a string or a scalar variable containing a string.

It is possible to save all variables, but not load all variables.

All variables must have been defined beforehand e.g.

LET $x = null

to define a scalar variable, or one of the LIST statements described above to define a list variable.

When loading a list variable from a .CSV file, the first value must be the list variable name, and the values separated by commas. This would appear as a horizontal list in Excel. For example:

$my_list, badger, penguin

Which could be loaded with the following SQL;

LIST $my_list;
LOAD $my_list FROM FILE 'C:/Data/animals.csv';