{ CREATE VIEW | CV | REPLACE VIEW } [ database_name. | user_name. ] view_name
[ ( column_name [,...] ) ] AS
{ view_specification | ( view_specification ) } [;]
- view_specification
[ locking_clause ... ]
[ as_of_clause ]
[ WITH { nonrecursive_with_modifier | recursive_with_modifier } [,...] ]
SELECT selection
FROM source [,...]
[ WHERE search_condition ]
[ GROUP BY group_specification [,...] ]
[ HAVING having_condition ]
[ QUALIFY qualify_condition ]
[ WITH CHECK OPTION ]
[ ORDER BY order_by_specification [,...] ]
- locking_clause
LOCKING item_to_lock [ FOR | IN ] lock_type [ MODE ] [ NOWAIT ]
- as_of_clause
AS OF calendar_function (
{ DATE | TIMESTAMP [ WITH TIME ZONE ] } expression [, system_calendar_name ] )
- nonrecursive_with_modifier
query_name [ ( column_name [,...] ) ] AS ( select_expression )
- recursive_with_modifier
RECURSIVE query_name [ ( column_name [,...] ) ] AS (
seed union_specification [union_specification ...]
)
- selection
[ DISTINCT | ALL ] [ TOP { n | m PERCENT } ] [ WITH TIES ]
{ * | sub_selection [,...] }
- source
{ [ database_name. | user_name. ] { table_name | view_name } [ [AS] correlation_name ] |
joined_table_source |
derived_table_source
}
- group_specification
{ ordinary_grouping_set |
empty_grouping_set |
rollup_list |
cube_list |
grouping_sets_specification
}
- order_by_specification
{ expression |
{ [ [ database_name. | user_name. ] table_name. ] column_name |
column_name_alias |
column_position
} [ ASC | DESC ]
}
- item_to_lock
{ [ DATABASE ] { database_name | user_name } |
[ TABLE ] [ database_name. | user_name. ] table_name |
[ VIEW ] [ database_name. | user_name. ] view_name |
ROW
}
- lock_type
{ ACCESS |
{ EXCLUSIVE | EXCL } |
SHARE |
READ [ OVERRIDE ] |
WRITE |
LOAD COMMITTED
}
- seed
{ SELECT | SEL } [ DISTINCT | ALL ] { * | seed_selection [,...] }
FROM seed_source
WHERE where_search_condition
[ GROUP BY group_specification [,...] ]
[ { HAVING | QUALIFY } having_qualify_search_condition ]
[ ORDER BY order_by_specification [,...] ]
- union_specification
UNION ALL { seed | recursive }
- sub_selection
{ expression [ [AS] expression_alias ] |
[ database_name. | user_name. ] table_name.*
}
- joined_table_source
joined_table { [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ]
JOIN joined_table ON search_condition |
CROSS JOIN single_table
}
- derived_table_source
( subquery ) [AS] derived_table_name [ ( column_name [,...] ) ]
- seed_selection
{ expression [ [AS] expression_alias ] |
table_name.*
}
- seed_source
{ table_name [ [AS] correlation_name ] |
joined_table_source |
derived_table_source
}
- recursive
{ SELECT | SEL } { * | seed_selection [,...] }
FROM { implicit_join [,...] | explicit_join }
WHERE where_search_condition
- implicit_join
{ query_name_specifier [...] | table_name } [ [AS] correlation_name_1 ]
- explicit_join
{ { query_name | join_table_name } LEFT [ OUTER ] JOIN joined_table |
join_table_name RIGHT [ OUTER ] JOIN { query_name | joined_table } |
query_name INNER JOIN joined_table |
join_table_name INNER JOIN query_name
} ON search_condition
- query_name_specifier
query_name [ [AS] correlation_name_2 ]