A View Alias provides the ability to create security views on SQL Server in an alternate schema.
Note
View Aliases are intended as a presentation object so that views over BI views could either mask a subset of the available data and/or have independent views in different databases/schema (SQL Server and Oracle). Because they are not stored as standard RED objects, they cannot be used as source tables for downstream processing.
The View Aliases tab enables you to define additional/replica views.
Fields | Description |
|---|---|
Add button | Enables you to add a View Alias which adds a new copy of the view. This enables you to define new View Alias which is an alternate definition that will ultimately exist as another database view with a different name, predicate and/or target location. |
Delete button | Enables you to delete a View Alias and remove its metadata details after closing the screen. |
View Alias Name | The view alias name that is used as the alternate database view name. |
View Alias Description | Description of the view alias. |
View Alias Predicate | Optional 'Where' clause to include in the alternate view definition. |
Target Connection | The name of the WhereScape RED connection that identifies the target location to create the alternate view. |
Database Type | [Read Only] The database type of the selected target connection. |
Target | The target that defines the database and schema for the table. Leave blank for the connection default. |
Target Database | [Read Only] Target database identifier (e.g. Oracle SID or TNS Name, Teradata TDPID) or Database Name (e.g. as in DB2 or SQL Server) to reference the alternate view. |
Target Schema | [Read Only] The target Database/User schema for the alternate view. Leave blank to use the default schema. |
View Alias Through Templates
RED supports template based Create View DDL Statement generation for view aliases. The CREATE VIEW statement needs to reference $OBJECT$ or $DATABASE$.$SCHEMA$.$TABLE$ tokens or use template variables to explicitly form the qualified view name. You cannot use the [TABLEOWNER] markup in the create statement as it cannot be correctly replaced at run-time for each View Alias. This means your existing create view templates may need updating to work with View Aliases.
For example, use:
CREATE OR REPLACE VIEW $OBJECT$
Instead of:
CREATE OR REPLACE VIEW [TABLEOWNER].[{{table.name}}]
Additional steps to create View Aliases for template based Create View DDL statement are the following:
- Configure a Create View DDL Template in the view object.
- Configure Drop View SQL in the connection.
The Default Create View DDL Template for new View Objects can be configured in the connection.
Note
The same CREATE VIEW DDL and Drop View SQL are used for both View Objects and View Aliases.
View Aliases are not meta objects and they cannot be found when applying [TABLEOWNER] markup, but [TABLEOWNER]can still be used elsewhere in the CREATE VIEW statement such as in FROM clause when used to markup source tables.
It is also possible to branch the logic in the Create View DDL Template by inspecting a new template variable table.viewAliasParent. This variable is only defined for view aliases, so the logic can be used only for View Aliases:
{% if table.viewAliasParent is defined %}
The table.viewAliasParent variable identifies the view object that owns the view alias.