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

{ CREATE | REPLACE } RECURSIVE VIEW [ database_name_1. | user_name_1. ] view_name_1
  ( column_name [,...] ) AS
    { view_specification | ( view_specification ) } [;]

view_specification

[ locking_specification [...] ]
[ date_specification ]
seed_statement
UNION ALL [ union_specification [...] ]
recursive_statement

locking_specification

LOCKING item_to_lock [ FOR | IN ] lock_type [ MODE ] [ NOWAIT ]

date_specification

AS OF calendar_function (
  { DATE date_expression | TIMESTAMP [ WITH TIME ZONE ] timestamp_expression }
    [, calendar_name ]
)

seed_statement

{ SELECT | SEL } [ DISTINCT | ALL ] { * | seed_statement_selection [,...] }
  FROM seed_statement_source [,...]
  [ WHERE search_condition ]
  [ GROUP BY grouping_specification [,...] ]
  [ HAVING having_condition ]
  [ QUALIFY qualify_condition ]

union_specification

{ seed_statement_specification [...] | recursive_statement UNION ALL }

recursive_statement

{ SELECT | SEL } [ ALL ] { * | recursive_statement_selection [,...] }
  FROM recursive_statement_source [,...]
  [ WHERE search_condition ] [;]

item_to_lock

{ [ DATABASE ] { database_name_2 | user_name_2 } |
  [ TABLE ] [ database_name_3. | user_name_3. ] table_name |
  [ VIEW ] [ database_name_4. | user_name_4. ] view_name_2 |
  ROW
}

lock_type

{ ACCESS |
  { EXCLUSIVE | EXCL } |
  SHARE |
  READ [ OVERRIDE ] |
  WRITE |
  LOAD COMMITTED
}

seed_statement_selection

{ expression | [ database_name. | user_name. ] table_name.* }

seed_statement_source

{ [ database_name. | user_name. ]
    { table_name [ [AS] correlation_name ] |

      joined_table
        { join_on | CROSS JOIN [ database_name. | user_name. ] single_table }
    } |
	
    ( subquery ) [AS] derived_table_name [ ( column_name [,...] ) ]
}

grouping_specification

{ ordinary_grouping_set |
  empty_grouping_set |
  rollup_list |
  cube_list |
  grouping_set_specification
}

seed_statement_specification

seed_statement UNION ALL

recursive_statement_selection

{ expression [ [AS] correlation_name ] | table_name.* }

recursive_statement_source

{ table_name [ [AS] correlation_name ] |

  joined_table
    { [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ]
        JOIN joined_table ON search_condition |

      CROSS JOIN single_table
    }
}

join_on

[ INNER | { LEFT | RIGHT | FULL } [ OUTER] ] JOIN
  [ database_name. | user_name. ] joined_table ON search_condition