CREATE/REPLACE VIEW Syntax | Teradata Vantage - CREATE VIEW and REPLACE VIEW Syntax - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™
{ 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 ]