==================================================================
VARIABLE REFERENCES IN VIEW DEFINITIONS
==================================================================
PRODUCT : R:BASE VERSION : 3.1 AND UP
CATEGORY: VIEWS SUBCATEGORY: VARIABLES
==================================================================
(From Ken Everett, Sequitur Systems, 117 Wallace Drive, Tullahoma,
TN 37388. You can reach him at (615) 454-9017. Ken is a R:BASE
instructor and application developer.)
When you use the CREATE VIEW command, the column list and WHERE
conditions in the SELECT clause can reference both system and global
variables. The data the resulting view displays can vary depending on
placement of parentheses around variables within the command. Examples
in this article are based on the sample CONCOMP database shipped with
R:BASE.
Column Expressions
==================
Columns in a view can be defined as the result of an expression in the
SELECT clause. For example, the following command creates a view with
a column (days) that contains the number of days between the transaction
date column and a date variable:
SET VAR vdate DATE = 10/15/91
CREATE VIEW since (transid, days) AS SELECT transid, +
(.vdate - transdate) FROM transmaster
This view could be used as the basis of an aging report, as was shown
by Bill Downall in the July/August 1991 issue of the R:BASE Exchange.
The first several records look like this when selected:
transid days
------- ----
4760 1015
4780 1009
4790 1008
4795 1006
4800 964
The parentheses around the date expression (.vdate - transdate) are not
required for the CREATE VIEW command to work, but if you leave the
parentheses out the results can be different.
When the expression is enclosed in parentheses, the variable reference
is stored as part of the view definition. If the parentheses are left
out, the current variable value is stored with the view definition.
Thus, with parentheses included in the example above, the values
displayed by the view for the column days are dependent on the current
value of the variable vdate. Without parentheses in the example above,
the values from the view for the column days are always relative to
10/15/91.
WHERE Conditions
================
Variable references used in WHERE clauses of views operate the same
way with respect to parentheses. For example, to create a view of
transactions for the customer whose ID number is in the variable
vcustid, use either of the following:
SET VAR vcustid INT = 101
CREATE VIEW Cust1Trans AS SEL * FROM transmaster +
WHERE custid = .vcustid
OR
SET VAR vcustid INT = 101
CREATE VIEW Cust2Trans AS SEL * FROM transmaster +
WHERE custid = (.vcustid)
A view based on the first CREATE VIEW command shows transactions for
customer 101 regardless of the current value of vcustid. In fact, the
variable vcustid does not need to exist to use the view Cust1Trans. A
view based on the second CREATE VIEW command cannot, however, be used
unless the variable vcustid exists and has a proper data type and value.
The second view definition gives different results as the value of
vcustid changes.
The SYSVIEWS System Table
=========================
When you use the CREATE VIEW command, R:BASE stores the view definition
in a system table called SYSVIEWS. You can look at the way R:BASE saves
your views by browsing or editing the contents of the SYSVIEWS table
with the BROWSE or EDIT commands. Be cautious about changing any of the
column values when you do this.
Conclusion
==========
When you use variable references in the SELECT clause of a CREATE VIEW
command, remember that the use of parentheses can affect the resulting
view. If a variable reference is enclosed in parentheses, the variable
name is stored in the actual view definition. If not, the value of the
variable at the time the view was created is stored in the view
definition.