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 column name. The column name is limited to 128 characters.
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. See Data Types.
(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.
MDI
Specifies to define a variable local to an MDI form.
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 variable name, which must be unique among the variable names within the database. The variable name is limited to 128 characters.
&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 Clause.
About the SET VARIABLE Command
Variables identify a changeable value. R:BASE provides four kinds of variables: global, static, 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 Variables.
Variable names 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. 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 datatype option 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 a 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 vM TEXT = 'Multi'
SET VARIABLE vP TEXT = 'Purpose'
SET VARIABLE vMP TEXT = '(vM + vP)'
SET VARIABLE vMPValue = .vMP
SET VARIABLE vMPCompute = &vMP
When the third command line runs, the variable vMP will contain (vM + vP). When the forth command line runs, variable vMPValue will also contain (vM + vP) because the dot tells R:BASE to set the value as an exact match to the contents of variable vMP. When the fifth command line runs, variable vMPCompute will contain MultiPurpose (the concatenation of Multi and Purpose) because the ampersand tells R:BASE to compute the contents of variable vMP.
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, v2 INTEGER
2.SET VARIABLE v1 = '((50 + 100)/ 2)'
3.SET VARIABLE v2 = &v1
4.SHOW VARIABLE
1.Sets the data types for variables v1 and v2 to TEXT and INTEGER, respectively.
2.Sets variable v1 to a text value that is a valid arithmetic expression.
3.Sets variable v2 to &v1.
4.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 = 12/23/2022 DATE
#TIME = 02:29 PM TIME
#PI = 3.14159265358979 DOUBLE
SQLCODE = 0 INTEGER
SQLSTATE = 00000 TEXT
#NOW = 12/23/2022 02:29 PM DATETIME
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.
In instances where you're building a dynamic SET VARIABLE command based on previous options made, 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 = &vcol IN &vtab
Enter the table and column names into the vtab and vcol variables 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.
NOTE: 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.
Using Local Variables within MDI Forms
The MDI keyword creates variables used in a MDI form, where with multiple instances of the MDI form, each form will have separate variables. Expressions that reference that variable will simply use the variable name. If a global variable is defined with the same name as a MDI variable, R:BASE will find the global variable first.
The link to which MDI form to use is based on the value of the RBTI_FORM_ALIAS variable, which holds the alias name. Different instances of MDI forms need different alias names.
When defining local variables to a MDI form, the "On Before Start" form EEP cannot be used for the SET VAR MDI ... command, as the form must be initialized first.
Examples
Defines the vText variable to have a TEXT data type.
SET VARIABLE vtext TEXT
Defines the vReal variable to have a REAL data type, and assigns it the value 100.9.
SET VARIABLE vReal REAL = 100.9
Defines the vNumer variable to have a NUMERIC data type having a precision of 9 and scale of 3.
SET VARIABLE vNumer NUMERIC (9,3)
Assigns the integer value 14322 to the vNum variable.
SET VARIABLE vNum = 14322
Assigns the value of the above vNum variable to the vTwo variable.
SET VARIABLE vTwo =.vNum
Assigns the value 03/25/2022 to the vLtrDate variable.
SET VARIABLE vLtrDate = ('12/25/21' + 90)
Assigns to the vFullName variable the value of the full name formed by concatenating the values in the vFirstName and vLastName variables The ampersand inserts a space between the two values.
SET VARIABLE vFullName = (.vFirstName & .vLastName)
Defines the vCounter INTEGER variable for a MDI form.
SET VARIABLE MDI vCounter INTEGER = 0
Assigns the first name, last name, and phone number to the variables from the row in the Contact where ContID is equal to 1001.
SET VARIABLE vContFName = ContFName, vContLName = ContLName, vContPhone = ContPhone IN Contact WHERE ContID = 1001
OR SQL compliant variation (See SELECT INTO):
SELECT ContFName, ContLName, ContPhone INTO vContFName INDI iv1, vContLName INDI iv2, vContPhone INDI iv3 FROM Contact WHERE ContID = 1001
