CREATE/REPLACE VIEW Syntax | Teradata Vantage - 17.05 - CREATE VIEW and REPLACE VIEW Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
{ 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 ]