Dotted Variables
1.The variable is used to hold a value.
2.Should be used when R:BASE is to use the "value" contained in the variable.
3.Should be used when R:BASE is to use the variable in a calculation or as a comparison value.
4.A rule of thumb for when to "dot" a variable is to always "dot" the variable when it is on the right side of the operator.
5.Dotting a variable basically turns it into a constant value. R:BASE looks only at the value of the variable when it is dotted. R:BASE doesn't look at the data type, just at the value the variable contains. That's why you can have a TEXT data type variable (result of a CHOOSE command, for example) containing a value that looks like a DATE and use that variable to compare to a DATE data type column or variable
6.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. There is a defined sequence that R:BASE goes through in guessing the possible data type. This is the order:
•INTEGER
•DOUBLE
•CURRENCY
•DATE
•TIME
•DATETIME
•BOOLEAN
7.In expressions, R:BASE checks the data type of a dotted variable. An expression is anything enclosed in parentheses. R:BASE verifies the data type in expressions to make sure the expression is valid. For example, an INTEGER cannot be added to TEXT.
8.Dotted variables are used in Form and Report expressions, just as they are in the SET VAR command. When on the right side of the operator, dot the variable.
9.Dotted variables are commonly used in WHERE clauses and in calculations with other variables.
Example 01:
SET VAR vDate DATE = 11/19/2001
SELECT * FROM TransMaster WHERE TransDate <= .vDate
The 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.When a variable contains part of a command, its name is prefaced with an ampersand, "&", and it is called an ampersand variable. The "&" in front of the variable name tells 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.
2.You cannot dot a variable when it contains part of a command - a table or column name, or an ORDER BY or WHERE clause.
3.Because an ampersand variable is part of a command, it can't be used inside parentheses. Parentheses indicate expressions, expressions are parsed separately from the rest of the command. You need to include the parentheses as part of the variable value. Sub-selects and IN lists are enclosed in parentheses and you can't use an ampersand variable inside them, you need to include the entire sub-select or IN list, including parentheses, as the variable value.
4.Ampersand variables are most often used to hold table and column names and WHERE and ORDER BY clauses. By using ampersand variables to hold column and table names, you can use the same command to select data from different tables. The CHOOSE command can be used with an ampersand variable to display menus of available tables and columns, as used in Example 01 below.
5.If a variable is used on the left side of the operator, an ampersand variable is to be used.
6.It is important to not confuse the ampersand that prefaces a variable name with the ampersand that is used to concatenate TEXT values.
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
