Dotted Variables
1.Used to hold values. Dotting a variable basically turns it into a constant value, and instructs R:BASE to use the "value" contained in the variable.
2.Used in calculations, WHERE clauses, and when comparing values
3.Used on the right side of the operator (=, <, >, <>, etc.)
4.When a variable is dotted, R:BASE only looks at the value of the variable not at the datatype. That's why a TEXT datatype variable (e. g. result of a CHOOSE or DIALOG command) containing a value that looks like a DATE can be used to compare to a DATE datatype column or variable.
5.In expressions, R:BASE checks the datatype of a dotted variable. An expression is anything enclosed in parentheses. R:BASE verifies the datatype in expressions to make sure the expression is valid. For example, an INTEGER cannot be added to TEXT.
6.Dotted variables are used in form/report/label expressions as they are in the SET VAR command. On the right side of the operator, dot the variable.
7.In order for R:BASE's expression processing to explicitly see the variable with a TEXT data type, it must be enclosed in parenthesis. Without the parenthesis, the data type is unknown. When R:BASE encounters an unknown data value, it needs to guess what it is. The below is the defined sequence order that R:BASE goes through in guessing the possible data type:
1)INTEGER
2)DOUBLE
3)CURRENCY
4)DATE
5)TIME
6)DATETIME
7)BOOLEAN
Example 01:
SET VAR vDate DATE = 11/19/2024
SELECT * FROM TransMaster WHERE TransDate <= .vDate
The above example selects all the records from the TransMaster table where the value in the column TransDate is less than or equal to the value contained in the variable vdate.
Example 02:
SET VAR vOrder INTEGER = 20001
SET VAR vAmount CURRENCY = NULL
SET VAR vShip TEXT = NULL
SET VAR vFreight CURRENCY = NULL
SET VAR vTax CURRENCY = NULL
SET VAR vState TEXT = NULL
SELECT NetAmount, ShipMethod, StateAbr INTO +
vAmount INDIC IvAmount, +
vShip INDIC IvShip, +
vState INDIC IvState +
FROM Orders WHERE OrderNum = .vOrder
IF vShip = 'AIR' THEN
SET VAR vFreight = $11.00
ELSE
SET VAR vFreight = $5.00
ENDIF
SET VAR vAmount = (.vAmount + .vFreight)
SELECT TaxRate INTO vTax INDIC IvTax +
FROM States WHERE StateAbr = .vState
SET VAR vAmount = (.vAmount+(.vAmount*.vTax))
On the right side of the equals sign (the operator), the variable is dotted. On the left side of the operator, in the IF and the SET VAR commands, the variable is referenced by its name only, it is not dotted.
Ampersand Variables
1.Used to contain part of a command (e. g. table/view/column name, WHERE clause, ORDER BY, etc.)
2.Should not be confused with the ampersand that is used to concatenate TEXT values
3.The ampersand (&) in front of the variable name instructs R:BASE that the variable contains part of the command, not a value, and the contents of the variable are used when parsing the command.
4.Because an ampersand variable is part of a command, it cannot be used inside parentheses. Parentheses indicate expressions, which are parsed separately from the rest of the command. When using sub-selects and IN lists, which are enclosed in parentheses, include the parentheses as part of the variable value.
5.By using ampersand variables to hold table and column names, the same command may be used to select data from different tables, when the ampersand variable value changes. Use the CHOOSE command to display menus with lists of available tables and columns.
6.If a variable is used on the left side of the operator (=, <, >, <>, etc.), an ampersand variable is to be used.
Example 01:
SET VAR vTable TEXT = NULL
SET VAR vColList TEXT = NULL
CLS
CHOOSE vTable FROM #TABLES TITLE 'Choose Table' +
CAPTION 'List of Tables' LINES 18 FORMATTED
CLS
CHOOSE vColList FROM #COLUMNS IN &vTable +
CHKBOX TITLE 'Choose Column(s)' CAPTION 'List of Columns' +
LINES 18 FORMATTED
The values selected from the CHOOSE commands are placed into variables. The variables might look like this:
vTable = Employee TEXT
vColList = Empid,EmpLname,EmpPhone,EmpExt TEXT
The variables are then used in any command that uses a table name or column list. Where you see Column names or Table/View name you can substitute an ampersand variable that contains the Column, Table or View name. The variables must be used as ampersand variables to tell R:BASE they contain part of the command.
Example 02:
BROWSE &vColList FROM &vTable
To prompt for an ORDER BY clause, use the CHKSORT option with the CHOOSE...FROM #COLUMNS.
Example 03:
SET VAR vOrderBy TEXT = NULL
CHOOSE vOrderBy FROM #COLUMNS IN &vTable +
CHKSORT TITLE 'Choose Column(s)' CAPTION 'Order By' +
LINES 18 FORMATTED
The CHKSORT option prompts for Ascending or Descending order just like the R:BASE sort menus. The variable contains ASC or DESC as well as the column names. It might look like this:
vOrderBy = EmpLname ASC,EmpFname ASC TEXT
In the command, add the keywords ORDER BY with the ampersand variable containing the columns to order by.
Example 04:
BROWSE &vColList FROM &vTable ORDER BY &vOrderBy
Example 05:
IF &vCodeInput CONTAINS 'New'
PAUSE 2 USING 'New Parts included.'
ELSE
PAUSE 2 USING 'New Parts are not included.'
ENDIF