REPLACE VIEW PRSN_GLOBAL_V.LSS_PRD_RSN AS LOCKING ROW FOR ACCESS SELECT srv_rsp_evt_id (INTEGER, NAMED srv_rsp_evt_id, Title 'Survey Response Event ID') , srv_rsp_txt (VARCHAR(1000),NAMED mn_rsn_not_prd_txt , TITLE 'Main Reason Not Productive' ) FROM EVENT_V.SRV_RSP where srv_qst_cde in ('8303','8307')
When I look at the ColumnTitle in dbc.columns or the fieldtitle in dbc.tvfields, the columns are null. However, when I run a query against the view in SQL assistant, these titles do show up. Is there another place in the Teradata data dictionary I should be looking for this information?
The only information stored in dbc.tvfields for views is the columnname, all other fields are NULLed. When you actually use the view, the parser will access the source code stored in dbc.tvm.createtext and resolve it down to the base tables to get the remaining info.
HELP COLUMN PRSN_GLOBAL_V.LSS_PRD_RSN.*; will return all info, but there's no way achieving that using a SELECT.
Thanks for the information! I did notice that the title information is shown for columns of tables. I was really hoping to be able to run a query against Teradata to get this information, but I guess not. We are trying to share metadata we store in Teradata with Business Objects, and the title information for views is vitally important. I guess I'll have to figure out another way to do that.
Teradata MDS will provide you this information and MDS is part of the Teradata Utility Pack so you already own it. You install MDS on a client and configure an ODBC connection to a Teradata database that will house the MDS repository of metadata. Then you tell MDS which Database(s) you want metadata extracted and it pulls the information from the Teradata Data Dictionary, including the Titles for view columns you need.
MDS 13.10 includes pre-defined SQLViews to read the metadata in the repository so from BO you would issue something like:
SEL ViewName, ColumnName, ColumnTitle FROM meta_ViewColumns WHERE DatabaseName='PRSN_GLOBAL_V' and ViewName='LLS_PRD_RSN' ORDER BY ColumnID
In this particular case the pre-defined view is called meta_ViewColumns.