Other Variables

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

Scalar 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”

The values assigned to the variables have to be single values, they cannot be the results of expressions e.g. you cannot say LET $diameter = $area / $pi

SCALARS

The keyword SCALARS on its own will display all the scalars.

SCALARS followed by a comma separated list of scalar variables will display just those variables e.g.

SCALARS $x, $z

Selection into scalar variables

If you have a SELECT clause which acts as a 'group by' for the entire table, it is possible to store the results in scalar variables e.g.

SELECT COUNT(*) INTO $mycount; SELECT(COUNT(x>0)) INTO $positivexcount

Scalar Expressions

It is possible to use the LET keyword to perform calculations on scalar variables as well as assign values. This is most likely to be useful when performing calculations on variables which have been set by select clauses as described above.

List variables

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

List variables are defined by a special clause with the format

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.

It is possible to declare a list variable but not to set any values by saying

LIST $widths

LIST $codes STRING

LIST $calendar DATE

These declare lists of numbers, strings and dates respectively.

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.

LOOKUPFN

LOOKUPFN(expression, clamp option, step option, list variable)

Implements a stepwise or piecewise-linear x->y lookup function with a NULL or numeric input expression (x) and returns a NULL or numeric value (y).

The function is specified by the list variable, which must be a number list containing a sequence of x,y pairs in monotonic ascending order of x, with a minimum of one such pair. If step option is true, the function is evaluated stepwise, otherwise it is evaluated as piecewise linear. If x is NULL, y is NULL. If the clamp option is true, x is clamped to the range of the function before evaluating y. Otherwise if x is outside the range of the function, y is 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 nth element in the list e.g.

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

AREF(2,$listname) will return 'two'.

Table variables

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.

Save and Load scalar and list variables

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

Saves and loads a list of scalar and list variables into a file, or loads them from a file. 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;

LIST $x;

etc.

This is currently the only way in which list variables can be changed.

e.g.

LET $x = 1.345;

LET $y = 'one two three';

LIST $xl = 1,23,456,7890;

LIST $yl = 'one','two','three','four,five';

LIST $zl = 'a','bb','ccc','ddd','eee','fff';

SAVE $x,$y,$xl,$yl,$zl TO FILE 'd:\temp\filename.dat'