SQL-J Language Reference
Page 18 of 121

CREATE VIEW statement

Views are virtual tables formed by a query. A view is a dictionary object that you can use until you drop it.

Views are not updatable.

Views cannot be created in the SYS schema.

Syntax

CREATE VIEW ViewName
    [ ( SimpleColumnName [, SimpleColumnName] * ) ]
AS Query

A view definition can contain an optional view column list to explicitly name the columns in the view. If there is no column list, the view inherits the column names from the underlying query, including any internally generated names for unnamed columns. All columns in a view must be uniquely named.

Example

CREATE VIEW v1 (col_sum, col_diff)
AS SELECT c1 + c2, c1 - c2
FROM Table1

CREATE VIEW BigStates (StateName)
AS VALUES 'Alaska', 'California', 'Texas'

CREATE VIEW Segments_SeatBookings
    (flight_id, segment_number, number_seats, travel_date)
AS SELECT flight_id, segment_number, number_seats,     travel_date
FROM FlightBookings
UNION ALL
SELECT segment_two_flight_id AS flight_id,
    segment_two_segment_number AS segment_number,
    number_seats, travel_date
FROM FlightBookings
WHERE segment_two_flight_id IS NOT NULL

Dependency System

View definitions are dependent on the tables, views, and aliases referenced within the view definition. DML (data manipulation language) statements that contain view references depend on those views, as well as the objects in the view definitions that the views are dependent on. In Version 3.0, a view is not dependent on indexes. Statements that reference the view depend on indexes the view uses; which index a view uses may change from statement to statement based on how the query is optimized. For example, given:

CREATE TABLE T1 (C1 DOUBLE PRECISION)

CREATE METHOD ALIAS sin
FOR java.lang.Math.sin

CREATE VIEW V1
AS
SELECT sin(c1)
FROM T1

the following SELECT:

SELECT *
FROM V1

is dependent on view V1, table T1, and method alias sin.