CREATE VIEW

Creates a named query that you can read like a table. Use a view to reuse a filter or calculation. The view reads the underlying data when queried.

Syntax

CREATE [ OR REPLACE ] VIEW <name>
AS <query>
[ COMMENT = '<string_literal>' ]

Parameters

• <name>: the view name.
• <query>: the ScopeQL query that supplies the view’s rows and columns.
• OR REPLACE: replaces an existing view with the new definition.
• COMMENT: an optional description of the view.

Examples

Use the request table from the query syntax sample data to create a view of failed requests:

CREATE VIEW failed_requests AS
    FROM reference_requests
    WHERE status >= 500
COMMENT = 'Requests that returned a server error'

Query the view:

FROM failed_requests
SELECT event_id, service, status
ORDER BY event_id

+----------+----------+--------+
| event_id | service  | status |
+----------+----------+--------+
| req-2    | checkout | 504    |
+----------+----------+--------+

To include client errors as well, replace its definition:

CREATE OR REPLACE VIEW failed_requests AS
    FROM reference_requests
    WHERE status >= 400
COMMENT = 'Requests that returned a client or server error'

Usage notes

• Views are read-only. They cannot be targets of INSERT, UPDATE, or DELETE; change the underlying table instead.
• View definitions cannot reference other views; expand the query to read base tables directly.
• These same definition rules apply when creating or updating a view through the Catalog API.