CODEWITHSUNDEEP
sqlteradatadynamic-sql
dave
dave

Reputation: 306

Teradata: How to create views dynamically from database tables

I am using Teradata BTEQ version 15.00. I have the following SQL code. The dynamical SQL is almost there, but the formet is a little off.

.Export Report File = CViews.sql

.Rtitle ''
 .Foldline on
 .Format Off
 .set heading '';
 .set heading off;
 .set UNDERLINE OFF;
 .Omit On 4,5

Select
 CASE When ColNo = 1
 THEN 'Replace View
$view_db_name.VW_'||Tbl.Tablename||' As locking row for access
Select ('
 Else '' END (Title '')
 , Cols.Columnname (Title '')
 , CASE WHEN RevColNo = 1 THEN ')
From $db_name.'||Tbl.Tablename||';'
 Else '' END (Title '')
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId) As ColNo
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId Desc) As RevColNo
 From DBC.Tables Tbl
 Join DBC.Columns Cols
 On Tbl.Databasename = Cols.Databasename
 And Tbl.Tablename = Cols.TableName
 Where Tbl.Databasename = '$db_name'
 And Tbl.Tablekind = 'T'
 Order By Tbl.Tablename, ColNo
 ;
.Export Reset

.Run File CViews.sql

Here are the results, but "locking row for access" was cut off, so i got errors when compiling SQL.

Replace View  VIEWS_TEST.VW_LOCATION                   As loc
   ID
   LOC_TYPE_ID
   NAME
   LATITUDE
   LONGITUDE
   ADDR1
   ADDR2
   CITY
   STATE
   COUNTRY

   )  From TABLES_TEST.LOCATION                  ;

You can see that the "As Loc" was cut off, so i got the following errors:

*** Failure 3707 Syntax error, expected something like a 'SELECT' keyword o
 r a 'LOCK' keyword or '(' or a 'TRANSACTIONTIME' keyword between the 'As' key word and the word 'loc'.
                Statement# 1, Info =81
 *** Total elapsed time was 1 second.

I tried different ways try to make it work, but failed.

Any suggestions?

Upvotes: 1

Views: 1801

Answers (2)

dave
dave

Reputation: 306

This is the working version on TD version 15.00. I added missing "," and change to the dbc.tablesV and dbc.ColumnsV.

.os rm CViews.sql
.Export Report File = CViews.sql
.set width 300
.Rtitle ''
 .Foldline on
 .Format Off
 .set heading '';
 .set heading off;
 .set UNDERLINE OFF;
 .Omit On 4,5
Select
 CASE When ColNo = 1
 THEN 'Replace View
$view_db_name.VW_'||Tbl.Tablename||' As locking row for access
Select '
 Else ', ' END (Title '')
 , Cols.Columnname (Title '')
 , CASE WHEN RevColNo = 1 THEN '
From $db_name.'||Tbl.Tablename||';'
 Else '' END (Title '')
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId) As ColNo
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId Desc) As RevColNo
 From DBC.TablesV Tbl
 Join DBC.ColumnsV Cols
   On Tbl.Databasename = Cols.Databasename
  And Tbl.Tablename = Cols.TableName
 Where Tbl.Databasename = '$db_name'
   And Tbl.Tablekind = 'T'
   and Tbl.Tablename not like 'WRK_%'
   and Tbl.Tablename not like 'ZZ_%'
 Order By Tbl.Tablename, ColNo
 ;
.Export Reset

.Run File CViews.sql

Upvotes: 0

anwaar_hell
anwaar_hell

Reputation: 776

.Export Report File = CViews.sql
.set width 200
.Rtitle ''
 .Foldline on
 .Format Off
 .set heading '';
 .set heading off;
 .set UNDERLINE OFF;
 .Omit On 4,5

Select
 CASE When ColNo = 1
 THEN 'Replace View
$view_db_name.VW_'||Tbl.Tablename||' As locking row for access
Select ('
 Else '' END (Title '')
 , Cols.Columnname (Title '')
 , CASE WHEN RevColNo = 1 THEN ')
From $db_name.'||Tbl.Tablename||';'
 Else '' END (Title '')
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId) As ColNo
 , Row_Number () Over(Partition By Tbl.Tablename
 Order By Cols.ColumnId Desc) As RevColNo
 From DBC.Tables Tbl
 Join DBC.Columns Cols
 On Tbl.Databasename = Cols.Databasename
 And Tbl.Tablename = Cols.TableName
 Where Tbl.Databasename = '$db_name'
 And Tbl.Tablekind = 'T'
 Order By Tbl.Tablename, ColNo
 ;
.Export Reset

.Run File CViews.sql

Upvotes: 1

Related Questions