Creates a view definition. A view is a relation that is not
physically stored. Rather, it is a virtual structure that points
to records from other relations. You define a view by specifying:
o A record selection expression to name the criteria for
selecting the relations and records
o A set of fields from those relations
Example:
DEFINE VIEW EMP_NAME OF E IN EMPLOYEES.
E.FIRST_NAME.
E.MIDDLE_INITIAL.
E.LAST_NAME.
END EMP_NAME VIEW.
1 – More
You need the Oracle Rdb READ and DEFINE privileges to the referenced
relations to use the DEFINE VIEW statement.
When the DEFINE VIEW statement executes, Oracle Rdb adds the view
definition to the physical database. If you have invoked the
database with the PATHNAME argument, the definition is also
stored in the data dictionary.
You must execute this statement in a read/write transaction. If
you issue this statement when there is no active transaction,
Oracle Rdb starts a read/write transaction implicitly.
Other users are allowed to be attached to the database when you
issue the DEFINE VIEW statement.
2 – Format
(B)0[m[4mDEFINE[m [4mVIEW[m qqq> name qqqqqk
lqqqqqqqqqqq<qqqqqqqqqj
mqqqwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqqqqk
mqq> [4mDESCRIPTION[m IS /* text */ qqqj x
lqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mOF[m qqq> rse qqqq> . qqqqqqqk
lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqj
mqwqwqqqqqqqqqqqqqqqqqqqwqqqqq> name-clause qqq> . qwqk
x mqq> /* text */ qqqqj x x
mqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqq> [4mEND[m qqqqwqqqqq>qqqqqqwqqq> VIEW qqqq> .
mqq> name qqqj
2.1 – name
Name of the view definition you want to create. When choosing a
name, follow these rules:
o Use a name that is unique among all view and relation names in
the database.
o Use any valid OpenVMS name. However, the name cannot end in a
dollar sign ($) or underscore (_).
o Do not use any Oracle Rdb reserved words.
2.2 – rse
A record selection expression that defines which rows of which
relations Oracle Rdb includes in the view. Ask for HELP on RSE for a
complete description of record selection expressions.
2.3 – name-clause
Specifies a field that you want to include in the view.
(B)0[mname-clause =
qqwqqq> context-var . field-name qqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqwqk
tqqq> local-field-name qq> [4mFROM[m qq> context-var . field-name qqu x
mqqq> local-field-name qq> [4mCOMPUTED[m [4mBY[m qqqq> value-expr qqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqq>
mwq> dtr-clause qqqqwqj
mqqqqqqq<qqqqqqqqqqj
3 – Examples
Example 1
You can define a view from a single relation:
DEFINE VIEW EMP_NAME OF E IN EMPLOYEES.
E.FIRST_NAME.
E.MIDDLE_INITIAL.
E.LAST_NAME.
END EMP_NAME VIEW.
This command file specifies a view definition derived from a
single relation, referring to three of its fields.
Example 2
You can also define a view using more than one relation:
DEFINE VIEW CURRENT_SALARY
OF SH IN SALARY_HISTORY CROSS
E IN EMPLOYEES OVER EMPLOYEE_ID WITH
SH.SALARY_END MISSING.
E.LAST_NAME.
E.FIRST_NAME.
E.EMPLOYEE_ID.
SH.SALARY_START.
SH.SALARY_AMOUNT.
END VIEW.
This command file defines a view from the EMPLOYEES and SALARY_
HISTORY relations. It uses the RSE to join the relations and
limit the view to current salaries. Then it lists the fields
required from each relation. These fields are referred to in
the view definition as is, using the same field names as in the
relation definition.
Example 3
You can give local field names to a view:
DEFINE VIEW EMP_JOB OF E IN EMPLOYEES
CROSS JH IN JOB_HISTORY OVER EMPLOYEE_ID
CROSS J IN JOBS OVER JOB_CODE
WITH JH.JOB_END MISSING.
CURRENT_ID FROM E.EMPLOYEE_ID.
CURRENT_NAME FROM E.LAST_NAME.
CURRENT_JOB FROM J.JOB_TITLE.
SUPERVISOR FROM JH.SUPERVISOR_ID.
END EMP_JOB VIEW.
The definition in this command file does the following:
o Joins the EMPLOYEES relation to JOB_HISTORY. This join links
employees to job history records.
o Joins JOB_HISTORY to JOBS. This join lets the view contain job
titles, instead of job codes.
o Uses the MISSING value expression. This clause specifies that
only the current job history records, where the JOB_END field
is empty, should be included in the view.
o Derives the view field names from the source relations but
gives them local names.
The following query uses the view defined in the preceding example:
&RDB& START_TRANSACTION READ_ONLY
&RDB& FOR CE IN EMP_JOB
GET
ID = CE.CURRENT_ID;
NAME = CE.CURRENT_NAME;
JOB = CE.CURRENT_JOB;
SUPER = CE.SUPERVISOR;
END_GET
&RDB& END_FOR
&RDB& COMMIT
Example 4
The COMPUTED BY field calculates the field in the view using a
field or fields from a component relation:
DEFINE VIEW SS_DEDUCTION OF E IN EMPLOYEES
CROSS SH IN SALARY_HISTORY OVER EMPLOYEE_ID
WITH SH.SALARY_END MISSING.
E.EMPLOYEE_ID.
E.SOCIAL_SECURITY.
SH.SALARY_AMOUNT.
SS_AMOUNT COMPUTED BY (SH.SALARY_AMOUNT * 0.065).
END SS_DEDUCTION VIEW.
This view definition computes a new "virtual" field from the
SALARY_AMOUNT field of SALARY_HISTORY.