Archives of the TeradataForum

Message Posted: Wed, 15 Aug 2007 @ 16:50:06 GMT

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

Martin,

I had developed a stored procedure for view creation based on Table Definition.

In this procedure all columns are projected.

I think you could use this and enhance as per your needs.

     REPLACE PROCEDURE SYSDBA.CREATE_VIEWS (
     IN SOURCE_DB VARCHAR(30),
     IN TARGET_DB VARCHAR(30),
     OUT MSG VARCHAR(32000)
     )
     --Proc level label
     P1:
     BEGIN
     DECLARE TABLE_NAME VARCHAR(30);
     DECLARE SQL_TEXT VARCHAR(10000);
     DECLARE CNT INTEGER;
     DECLARE COL_NM VARCHAR(10000) ;
     DECLARE FIRSTTM CHAR(1) DEFAULT 'Y';
     DECLARE EXIT HANDLER FOR SQLEXCEPTION
     SET MSG = SQLcode;
     -- Check source database exists
     SELECT
     COUNT(*) INTO :CNT
     FROM (SELECT
     DatabaseName
     FROM DBC.DATABASES
     WHERE UPPER(DatabaseName) = UPPER(:SOURCE_DB) ) DT1;
     IF CNT = 0 THEN
     SET MSG = 'Database '|| SOURCE_DB||' does not exist!';
     END IF ;
     -- Check target database exists
     SELECT COUNT(*) INTO :CNT
     FROM (SELECT
     DatabaseName
     FROM DBC.DATABASES
     WHERE UPPER(DatabaseName) = UPPER(:TARGET_DB) ) DT1;
     IF CNT = 0 THEN
     SET MSG = 'Database '|| TARGET_DB||' does not exist!';
     LEAVE P1;
     END IF ;
     L1:
     FOR L1GETTABLES AS GETINDEX CURSOR FOR
     SELECT DISTINCT
     TBL.DatabaseName
     ,TBL.TableName
     FROM
     (SELECT
     T.TableName
     ,T.DatabaseName
     ,C.ColumnName
     ,ROW_NUMBER() OVER(PARTITION BY T.DatabaseName, T.TableName ORDER BY
     ColumnID ASC) AS ColumnId
     FROM DBC.COLUMNS C JOIN DBC.TABLES T
     ON T.DatabaseName = C.DatabaseName
     AND T.TableName = C.TableName
     WHERE T.DatabaseName= :SOURCE_DB
     AND T.TableName IN ('INSTAL_CLAIM_FACT')
     AND T.TableKind='T' ) TBL
     LEFT JOIN
     (SELECT
     V.TableName
     ,C.ColumnName
     ,ROW_NUMBER() OVER(PARTITION BY V.DatabaseName, V.TableName ORDER BY
     ColumnID ASC) AS ColumnId
     FROM DBC.COLUMNS C JOIN DBC.TABLES V
     ON V.DatabaseName = C.DatabaseName
     AND V.TableName = C.TableName
     WHERE V.DatabaseName= :TARGET_DB
     AND V.TableKind='V') VW
     ON TBL.TableName = VW.TableName
     AND TBL.ColumnName = VW.ColumnName
     AND TBL.ColumnID = VW.ColumnID
     WHERE VW.ColumnName IS NULL
     AND Tbl.TableName IN ('INSTAL_CLAIM_FACT')
     ORDER BY 1, TBL.ColumnID
     DO
     SET TABLE_NAME = L1GETTABLES.TableName;
     SET FIRSTTM ='Y' ;
     SET SQL_TEXT = ' REPLACE VIEW ' || TARGET_DB || '.' || TABLE_NAME || ' AS ' || X'0D';
     SET SQL_TEXT = SQL_TEXT || ' LOCKING ROW FOR ACCESS ' ||X'0D' ;
     SET SQL_TEXT = SQL_TEXT || 'SELECT ' || X'0D';
     L2:
     FOR L2GETCOLUMNS AS GETINDEX CURSOR FOR
     SELECT
     ROW_NUMBER() OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnId ASC) Seq
     ,COUNT(*) OVER(PARTITION BY DatabaseName, TableName ORDER BY ColumnId ASC) Cnt
     ,CASE WHEN Seq < Cnt THEN TRIM(ColumnName) || ','
     ELSE TRIM(ColumnName) End AS C2
     FROM DBC.COLUMNS
     WHERE DatabaseName = :SOURCE_DB
     AND TableName = :TABLE_NAME
     ORDER BY COLUMNID
     DO
     SET COL_NM = L2GETCOLUMNS.C2 || X'0D';
     END FOR L2;
     SET SQL_TEXT =SQL_TEXT || COL_NM;
     SET SQL_TEXT = SQL_TEXT || ' From ' || TRIM(SOURCE_DB ) || '.' ||
     TRIM(TABLE_NAME) || ';' || X'00';
     CALL DBC.SYSEXECSQL (:SQL_TEXT);
     END FOR L1;
     SET SQL_TEXT = 'GRANT SELECT ON ' || TRIM(SOURCE_DB ) || ' TO ' ||
     TRIM(TARGET_DB) || ' WITH GRANT OPTION;';
     CALL DBC.SYSEXECSQL (:SQL_TEXT);
     SET SQL_TEXT = 'GRANT INSERT ON ' || TRIM(SOURCE_DB ) || ' TO ' ||
     TRIM(TARGET_DB) || ' WITH GRANT OPTION;';
     CALL DBC.SYSEXECSQL (:SQL_TEXT);
     SET SQL_TEXT = 'GRANT DELETE ON ' || TRIM(SOURCE_DB ) || ' TO ' ||
     TRIM(TARGET_DB) || ' WITH GRANT OPTION;';
     CALL DBC.SYSEXECSQL (:SQL_TEXT);
     SET MSG = ' Views creation process has successfully completed';
     END;

Thanks,

Vinay Bagare

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