Please enable JavaScript to view this site.

R:BASE 11 Help

Navigation: Reference Index

Dotted versus Ampersand Variables

Scroll Prev Top Next More

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