USER MANUALS

VIEW_DEPENDENCIES

Description

The stored procedure VIEW_DEPENDENCIES returns a list of the dependencies of one or more views (base and derived views).

This procedure allows you to obtain programmatically the same information that the Administration Tool displays in the “Tree view” dialog of a derived view.

Syntax

VIEW_DEPENDENCIES (
      input_view_database_name : text
    , input_view_name : text
)

  • input_view_database_name: name of the database whose views you want to obtain the dependencies of.

  • input_view_name: name of the view you want to obtain its dependencies from.

Both parameters are optional:

  • If both parameters are NULL, the procedure returns the dependencies of all views of all the databases.

  • If input_view_database_name is NULL and input_view_name is not, the procedure returns the dependencies of all the views with this name, that are located on any database.

  • If input_view_name is NULL and input_view_database_name is not, the procedure returns the dependencies of all the views of this database

Each row of the result of this procedure represents a dependency between a derived view (the field “view_name”) and another element (“dependency name”), which can be a derived view, base view or a data source.

If a view depends on a view from another database, the procedure also returns the view from this other database.

The output schema has the following fields:

  • view_database_name: name of the database that the view on the field “view_name” belongs to.

  • view_name: name of the derived view.

  • view_identifier: identifier of the view. It is autogenerated by the stored procedure and may change between executions of the procedure. See the example below to understand how to use this value.

  • view_type: type of view. I.e. Base view, Select, Join, Interface, etc. When the type of the view is join, the value of this field includes the following information, in this order:

    • Type of join: it can be “Inner”, “Left outer”, “Right outer” or “Full outer”.

    • Join method: it can be “Any”, “Hash”, “Nested”, “Nested parallel” or “Merge”. “Any” means that the user did not select a method to execute the join when creating the view. Therefore, the Execution Engine will automatically select a method, unless one is selected in the “Execution plan” tab of the queried view.

    • Order of the join: it can be “Any”, “Ordered” or “Reverseorder”. “Any” means that the user did not select an order to execute the join when creating the view. Therefore, the Execution Engine will automatically select an order, unless one is selected in the “Execution plan” tab of the queried view.

  • private_view: true if the view on the field “view_name” is private; false otherwise.

    A view is private when “view_name” represents a join, minus, intersect view of another join/minus/intersect view and the view was internally by Virtual DataPort and not by the user.

    For example, if you create a derived view called J_V that joins three views: (V1 JOIN V2) JOIN V3, internally Virtual DataPort creates a join view of V1 and V2. J_V will be the result of joining this internal view with V3. In this example, the join of V1 and V2 is a private view and J_V is public.

    The information about private views allows you to know in which order a join/minus/intersect view of more than two views is performed.

  • dependency_database_name: name of the database that the dependency belongs to.

    The value of this field will always be the same as “view_database_name”.

  • dependency_name: name of the element (view or data source) on which the view on the “view_name” field depends.

  • dependency_identifier: internal identifier of the element (view or data source) on which the view on the “view_name” field depends.

    It is autogenerated by the stored procedure and may change between executions of the procedure.

  • dependency_type: type of element on which the view on the “view_name” field depends.

  • depth: distance between the view of the field “view_name” and the dependency.

    When a row represents a direct dependency, the value of this field is 1. Direct dependencies are dependencies between views that directly depend on each other. E.g. a base view and a projection view created over this base view have a direct dependency.

    When a row represents a transitive dependency, the value of this field is greater than 1. Transitive dependencies are dependencies between views that depend on each other, but there are other views in between. Let us say that we have created a selection view called P_V created over a base view BV1. In this example, there is a transitive dependency between P_V and the data source of the view BV1.

Example

Let us say that we have created a view called sales_info like this:

(employees LEFT OUTER JOIN orders) INNER JOIN products

The following image is a screenshot of the result of executing CALL VIEW_DEPENDENCIES ('customer_360', 'sales_info');

For formatting purposes, some columns are not displayed.

Sample result of executing the procedure VIEW_DEPENDENCIES

Sample result of executing the procedure VIEW_DEPENDENCIES

To understand the result, take into account the following:

  • The rows #3 to #6 represent the dependencies of a private view (the field “private” is true in these rows). This private view is the left outer join between employees and orders.

  • The rows #8 till the last one describe the dependencies of the view sales_info. Note that all of them have the same “view_identifier”. All the dependencies of a view have the same “view_identifier”.

  • The rows whose value of “dependency_identifier” is 5 represent the direct or transitive dependencies with the JSON data source “Employees”:

    • Row #1 represents the direct dependency between the base view “Employees” and the JSON data source. The dependency is direct because the value of the “depth” field is 1.

    • Row #4, the transitive dependency between the private view and the JSON data source. The dependency is transitive because “depth” is greater than 1.

    • Row #10, the transitive dependency between the view “sales_info” and the JSON data source. This dependency is also transitive because “depth” is 2. Note that “depth” is the same for the dependency between the private view and the data source, and between the dependency between “sales_info” and the data source.

Privileges Required

The information returned by the procedure changes depending on the type of user that executes the procedure:

  • Administrators: the procedure returns information about all the dependencies.

  • Administrators of a database: the procedure returns information about all the views of that database. However, it does not return any information about the dependencies of views of other databases.

  • Users that have READ privileges over a database: the procedure returns information about the dependencies of all views of that database. However, it does not return dependencies between views and data sources.

  • Users that have READ privileges over some views of a database: the procedure returns information about the dependencies of the fields of the views on which the user has the READ privilege granted. If the user does not have READ privilege granted over the dependency, the value of the field “dependency_type” will be “No Privileges”. The procedure does not return the dependencies between views and data sources.

Add feedback