Views

A view is a virtual table based on the result set of a SQL statement. It derives its fields from a SELECT statement. You can do anything with a OmniSci view query that you can do in a non-view OmniSci query.
Nomenclature Constraints
View object names must use the NAME format, described in regex notation as:
1
[A-Za-z_][A-Za-z0-9\$_]*
Copied!
CREATE VIEW
Creates a view based on a SQL statement.
Example
1
CREATE VIEW view_movies
2
AS SELECT movies.movieId, movies.title, movies.genres, avg(ratings.rating)
3
FROM ratings
4
JOIN movies on ratings.movieId=movies.movieId
5
GROUP BY movies.title, movies.movieId, movies.genres;
Copied!
You can describe the view as you would a table.
1
\d view_movies
2
VIEW defined AS: SELECT  movies.movieId, movies.title, movies.genres,
3
avg(ratings.rating) FROM ratings JOIN movies ON ratings.movieId=movies.movieId
4
GROUP BY movies.title, movies.movieId, movies.genres
5
Column types:
6
    movieId INTEGER,
7
    title TEXT ENCODING DICT(32),
8
    genres TEXT ENCODING DICT(32),
9
    EXPR$3 DOUBLE
Copied!
You can query the view as you would a table.
1
SELECT title, EXPR$3 from view_movies where movieId=260;
2
Star Wars: Episode IV - A New Hope (1977)|4.048937
Copied!
DROP VIEW
Removes a view created by the CREATE VIEW statement. The view definition is removed from the database schema, but no actual data in the underlying base tables is modified.
Example
1
DROP VIEW IF EXISTS v_reviews;
Copied!

Tables

Policies

Last modified 4mo ago

Export as PDF

Copy link

Contents

Nomenclature Constraints