1  DEFINE_VIEW
   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.
 

2  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)0DEFINE VIEW qqq> name qqqqqk
      lqqqqqqqqqqq<qqqqqqqqqj
      mqqqwqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqqqqk
          mqq> DESCRIPTION IS /* text */ qqqj     x
      lqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj
      mqq> OF qqq> rse qqqq> . qqqqqqqk
      lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqj
      mqwqwqqqqqqqqqqqqqqqqqqqwqqqqq> name-clause qqq> . qwqk
        x mqq> /* text */ qqqqj                           x x
        mqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj x
      lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
      mqq> END qqqqwqqqqq>qqqqqqwqqq> VIEW qqqq> .
                   mqq> name qqqj

 

3  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.
 

3  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.
 

3  name-clause
   Specifies a field that you want to include in the view.

 (B)0name-clause =
 
 qqwqqq> context-var . field-name qqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqwqk
   tqqq> local-field-name qq> FROM qq> context-var . field-name qqu x
   mqqq> local-field-name qq> COMPUTED BY qqqq> value-expr qqqqqqqj x
   lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
   mqqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqq>
        mwq> dtr-clause qqqqwqj
         mqqqqqqq<qqqqqqqqqqj

 

2  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.