November 19, 2001
====================================================================
>From the Edge: Understanding Dotted vs. Ampersand Variables
Section: Variables
Chapter: Running R:BASE Your Way!
Platform: R:BASE 2000 (ver 6.5++) for DOS/Windows
Build: 1.842xRT03 and Higher
====================================================================
Dotted Variables:
----------------
01. Variables used to hold values.
02. When you want R:BASE to use the "value" contained in
the variable.
03. When you use a variable in a calculation or as a
comparision value, you use "dot" variable.
04. 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.
05. 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 datatype, just at the value
the variable contains. That's why you can have a TEXT datatype
variable (result of a CHOOSE or DIALOG command, for example)
containing a value that looks like a DATE and use that variable
to compare to a DATE datatype column or variable.
06. 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. You can't add an INTEGER to a TEXT for example.
07. Dotted variables are used in Form and Report expressions as they
are in the SET VAR command. On the right side of the operator,
dot the variable.
08. 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
selects all the records from the table TransMaster 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:
-------------------
01. You can't dot a variable when it contains part of a command
- a table or column name, or an ORDER BY or WHERE clause.
02. 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.
03. Don't confuse the ampersand that prefaces a variable name with
the ampersand that is used to concatenate TEXT values.
04. 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.
05. 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 displays
menus of available tables and columns.
Example 01:
SET VAR vTable TEXT = NULL
SET VAR vColList TEXT = NULL
CLS
CHOOSE vTable FROM #TABLES AT CENTER,CENTER TITLE 'Choose Table' +
CAPTION 'List of Tables' LINES 18 FORMATTED
CLS
CHOOSE vColList FROM #COLUMNS IN &vTable AT CENTER,CENTER +
CHKBOX TITLE 'Choose Column(s)' CAPTION 'List of Columns' +
LINES 18 FORMATTED
The values selected from the menus 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 instead
of CHKBOX on the CHOOSE...FROM #COLUMNS.
Example 03:
SET VAR vOrderBy TEXT = NULL
CHOOSE vOrderBy FROM #COLUMNS IN &vTable AT CENTER,CENTER +
CHKSORT TITLE 'Choose Column(s)' CAPTION 'Order By' +
LINES 18 FORMATTED
The CHKSORT option prompts for Ascending or Descending 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, follow the keywords ORDER BY with the ampersand
variable containing the columns to order by.
Example 04:
BROWSE &vColList FROM &vTable ORDER BY &vOrderBy
---
Download PDF version of this article at:
http://www.rbase.com/FromTheEdge/DottedVsAmpersandVariables.pdf
Send your constructive comments to: mailto:[EMAIL PROTECTED]
Very Best Regards,
Razzak.
===================================-============================
Official R:BASE List Server: mailto:[EMAIL PROTECTED]
RBTI Events/Training: http://www.rbase2000.com/events
R:DCC Members: http://www.rbase2000.com/rdcc
================================================================
R:BASE, Oterro & R:Tango are registered trademarks of RBTI.
==================================-=============================
