Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 02 Aug 2005 @ 16:37:41 GMT


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


Subj:   Correlating Multiple Views and Join Views to Base Tables
 
From:   Henderson, John

Hi All,

I'm involved in a project where I need to be able to match the view column name with it's associated base table column name, including those views where a join is occurring between two tables with common base column names. Here's an example using two views against a common set of base tables.

View DDL:

     Create View DBSV1.View1
     (Cust_Acct, Cust_Name, Txn_Ttl)
     As Locking DBS1.Table1 for Access Locking DBS2.Table2 for Access
     Select a.customer_account, a.customer_name, b.customer_txn_ttl
     From DBS1.Table1 A Join DBS2.Table2 B
     On A.customer_account = b.customer_account
     ;

View DDL:

     Create View DBSV1.View2
     (Cst_Acc, Cst_Nm)
     As Locking DBS1.Table1 for Access
     Select a.customer_account, a.customer_name
     From DBS1.Table1
     ;

Ultimately, what I want to end up with is a list of columns on base tables, and every occurrence where that base table column is accessed in a view (whether under the same name or a different name), with the associated column names from the views, like:

     DBS1.Table1.customer_account is read by DBSV1.View1.Cust_Acct
     DBS1.Table1.customer_account is read by DBSv1.View2.Cst_Acc
     DBS1.Table1.customer_name is read by DBSV1.View1.Cust_Name
     DBS1.Table1.customer_name is read by DBSV1.View2.Cst_Nm
     DBS1.Table2.customer_txn_ttl is read by DBSv1.View1.Txn_Ttl

What I'm trying to determine are the base table column names that are not accessed by users, regardless of the seemingly endless number of views which may read a specific column, and this is a precursor step needed to tie the view column names in queries back to the source base table column names to see where columns are and aren't accessed.

I started under the impression that column ID's were unique, but I'm now given to they are unique within the database and tablename. During my trials, I would get miss-matches because the same column ID would be on Table1 and Table2, but be non-related columns (Customer_Account on one table, and Activity_Date on the second table, but with the same column ID in their associated base tables).

Does anyone have an example of SQL that has successfully developed these results, or any suggestions as to approach for acquiring these results successfully? Any information or tidbits would be most welcome.


Thanks,

John



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016