Archives of the TeradataForum

Message Posted: Wed, 15 Aug 2007 @ 15:45:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I am trying to dynamically create a view based on an existing view, blanking out columns if they exist on a control table.

For example, I want to copy viewdb.view1 into controlviewdb.view1 blanking out col1 (as col1 is in control table):

     Viewdb.view1
     REPLACE VIEW Viewdb.view1
      AS
      SELECT * FROM tab.tab1
      where col3 = 'abc'
      WITH CHECK OPTION

Becomes

     REPLACE VIEW controlviewdb.view1
      AS
      SELECT col1   as XXXXXX
            ,col2
            ,col3
      FROM tab.tab1
      where col3 = 'abc'
      WITH CHECK OPTION

Even though the initial view has Sel * I need to get the underlying columns behind it and make sure they are kept in the same order as the table definition.

Can anyone offer any advice on the best approach?

Thanks in advance

	<Prev	Next>		<<First	<Prev	Next>	Last>>