SET VARIABLE

SET VARIABLE

Use the SET VARIABLE command to define or redefine a variable value and/or data type.

Options

,
Indicates that this part of the command is repeatable.

colname
Specifies a 1 to 18 character column name. In a command, you can enter #c, where #c is the column number shown when the columns are listed with the LIST TABLES command. In an SQL command, a column name can be preceded by a table or correlation name and a period (tblname.colname).

datatype
Specifies an R:BASE data type for the variable.

(expression)
Determines a value using a text or arithmetic formula. The expression can include other columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.

IN tblview
Specifies a table or view.

NULL
Sets the variable equal to NULL.

value
Sets the variable equal to a specified value. A value is a constant amount, text string, date, or time, or the value assigned to varname.

varname
Specifies a 1 to 18 character variable name.

&varname
Sets the first variable equal to the exact contents of a second variable; the ampersand tells R:BASE to evaluate the contents of the variable first.

For example, if varname is the text string (2+3), then &varname is the value 5.

.varname
Sets the first variable equal to the exact contents of a second variable.

For example, if varname is the text string (2+3), then .varname is (2+3).

WHERE clause
Limits rows of data. For more information, see the "WHERE" entry.

About the SET VARIABLE Command

Variables identify a changeable value. R:BASE provides three kinds of variables: global, error, and system. The SET VARIABLE command defines global variables, which are temporary variables that exist within R:BASE, but are not part of any database. Global variables remain in memory until you clear them or exit from R:BASE. R:BASE sets error and system variables internally.

Global variables have several uses: they can provide a temporary value in a command, hold the result of a calculation, act as a counter, or capture keyboard entries for use with menus or screens. The most common method of defining variables is to assign the variable value with the SET VARIABLE command. For information about defining global variables, see the "Variables" entry.

Variable names can be a maximum of 18 characters, and have the following restrictions:

The variable name is not an R:BASE reserved word.

The variable name begins with a letter, contains only letters, numbers, and the following special characters: #, $, _ , and %.

It is good programming practice to always define the data type for the variable before assigning it a value, unless you are setting a variable to a column value or using the variable in the CHOOSE command.

When defining an variable as a text string, enclose the text string in single quote marks (or the current QUOTES character); otherwise, it might be interpreted as an arithmetic expression.

Assigning a Data Type to a Variable

The datatype option refers to one of the valid R:BASE data types: BIT, BITNOTE, CURRENCY, DATE, DATETIME, DOUBLE, INTEGER, LONG VARBIT, LONG VARCHAR, NOTE, NUMERIC, REAL, TIME, VARBIT, OR VARCHAR. You can define a variable to have a NOTE data type, but R:BASE treats it as TEXT for most uses. You can also specify the precision and scale for NUMERIC data types.

The datatype option creates a variable, determines its data type, and sets its value to null. Use this option to define a variable's data type before assigning a value to the variable. To set multiple variables in the same command, separate the variables by a comma or the current delimiter.

For an existing variable, you can use the datatypeoption to change the data type, but it is recommended to use one of the conversion functions. If you change the data type, the new data type must be compatible with the current variable value; if the variable is not compatible, R:BASE displays an error message and leaves the value and data type unchanged. If you change a variable with a TEXT data type to a non-compatible data type, R:BASE changes the value to null.

Assigning a Value to a Variable

The value option is a data value or constant, such as 10, TOM, 3.1416, or $17.23. If the variable already exists, any new value must have a data type that is compatible with that variable. If the variable does not exist, R:BASE defines the variable's data type.

You can also define the variable's data type in this command before assigning it a value.

Setting the Value of a Variable to Another Variable

When you set the variable to the value of another variable, the second variable must be a dot variable (.) or an ampersand (&) variable.

When you precede a variable with a dot (.), R:BASE uses the value stored in the variable as if it were a constant.

When you precede a variable with an ampersand (&), R:BASE first evaluates the value contained in the ampersand variable. For example, consider the following uses of the command:

SET VARIABLE v1 TEXT = '(A + B)'
SET VARIABLE v2 = .v1
SET VARIABLE v3 = &v1

When the first command line runs, variable v1 will contain (A + B). When the second command line runs, variable v2 will also contain (A + B) because the dot tells R:BASE to set the value as an exact match to the contents of variable v1. When the third command line runs, variable v3 will contain AB (the concatenation of A and B) because the ampersand tells R:BASE to compute the contents of variable v1.

As shown in the example above, an ampersand variable can contain one command or part of one command, such as an expression. The first variable is set to the computed value of the ampersand variable. Below is an example:

1.	SET VARIABLE v1 TEXT

2.	SET VARIABLE v2 INTEGER

3.	SET VARIABLE v1 = '(50 + 100 / 2)'

4.	SET VARIABLE v2 = &v1

5.	SHOW VARIABLE

Sets the data types for variables v1 and v2 to TEXT and INTEGER, respectively.

Sets variable v1 to a text value that is a valid arithmetic expression.

Sets variable v2 to &v1.

Displays the value of all variables, including the system variables.

R:BASE computes the expression contained in v1 and assigns the calculated value to v2. When R:BASE sees a variable name preceded by ampersand, it treats the contents of the variable as if it was entered from the keyboard. The SHOW VARIABLE display would like the following:

Variable	= Value	Type
#date	= 04/12/94	DATE
#time	= 22:52:52	TIME
#pi	= 3.14159265358979	DOUBLE
sqlcode	= 0	INTEGER
v1	= (50 + 100 / 2)	TEXT
v2	= 75	INTEGER

Setting a Variable to an Expression

An (expression) can be either an arithmetic operation that combines two or more items in an arithmetic computation, or a string expression that concatenates two or more text items, or uses a TEXT function. The items can be values or the values contained in variables.

If you do not predefine the data type of a variable, the original data type of each item determines the data type of the result. For example, if you add a variable that has an INTEGER data type to a variable that has a REAL data type, the resulting variable has a REAL data type unless you define the result to be an INTEGER data type.

If any item in an arithmetic expression is null, the result will be null unless you specify SET ZERO ON.

Assigning Column Values in a Table or View

If you specify a table or view in a SET VARIABLE command, you can include an optional WHERE clause to indicate which row to use. If you do not include the WHERE clause, R:BASE uses the value for the column in the first row.

You must have SELECT privileges on the table to use this form of SET VARIABLE.

You must use an ampersand variable in place of a column or table name, for example:

CHOOSE vtab FROM #TABLES
CHOOSE vcol FROM #COLUMNS IN &vtab
SET VARIABLE vnewpr TO &vcol IN &vtab

Enter the table and column names into the vtab and vcolvariables first. You can do this by using the CHOOSE #TABLES and CHOOSE #COLUMNS commands, as shown in the above example. The CHOOSE command displays a menu of tables or columns from which to choose. By using ampersand variables to hold the table and column names, you can use the same SET VARIABLE command to get values from different columns in a table or from different tables. Each time SET VARIABLE requests a column, it retrieves information from the first row in the table stored in &vtab.

You can define multiple variables with a single SET VARIABLE command when you set the value of the variables to the value of columns in a table. However, when capturing column data into variables, it is better to use the SELECT command, specifically; SELECT INTO. SELECT INTO is the SQL compliant command when capturing table data into variables.

Examples

The following table provides examples of the SET VARIABLE command.

SET VARIABLE Examples

Examples	Description
SET VARIABLE vtext TEXT	Defines the vtext variable to have a TEXT data type.
SET VARIABLE vreal REAL = 100.9	Defines vreal variable to have a REAL data type, and assigns it the value 100.9.
SET VARIABLE vnumer NUMERIC (9,3)	Defines the vnumer variable to have a NUMERIC data type having a precision of 9 and scale of 3.
SET VARIABLE vnum = 14322	Assigns the integer value 14322 to the vnum variable.
SET VARIABLE VTWO =.VNUM	Assigns the value of the vnum variable to the vtwo variable.
SET VARIABLE V3 = &V4	Assigns the computed value of v4 to the v3 variable.
SET VARIABLE vltdate = ('12/25/93' + 90)	Assigns the value 03/25/94 to the vltdate variable.
SET VARIABLE vfulln = + (.VFIRSTN & .VLASTN)	Assigns to the vfulln variable the value of the full name formed by concatenating the values in the vfirstn and vlastn variables The ampersand inserts a space between the two values.
SET VARIABLE v1 = col1, v2 = col2, v3= col3 IN tbl1 WHERE col1 = 'Smith' OR SQL compliant variation: SELECT col1, col2, col3 INTO v1 INDI iv1, v2 INDI iv2, v3 INDI iv3 FROM tbl1 WHERE col1 = 'Smith' See SELECT INTO	Assigns Smith to the variable v1; the value of column col2 to v2; and the value of column col3 in tbl1, from the row where col1 contains Smith, to variable v3.