Titles for columns in a view not appearing

Database

Titles for columns in a view not appearing

I have the following SQL to create a view

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?

Thanks!
5 REPLIES

Re: Titles for columns in a view not appearing

The "help column..." command shows the title. Where is this command getting it's information?
N/A

Re: Titles for columns in a view not appearing

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.

Dieter

Re: Titles for columns in a view not appearing

Dieter,

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.

Bummer...
N/A

Re: Titles for columns in a view not appearing

Teradata MetaData Services (MDS) could probably provide all the infomation you need, but this would be a major project.

Dieter
Teradata Employee

Re: Titles for columns in a view not appearing

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.

I hope that helps!