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

{ 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 ]