I currently have a view definition which has a number of derived columns requiring column names which meet technical standards, and user-friendly titles. A simplified version of the code I am using follows:
replace VIEW ODS_NMC_ADT_GRP_ADT_SCTN_MS_CV
LOCKING ROW FOR ACCESS
SELECT COALESCE(BSNSS_EFFCTV_END_DT, CAST('31-12-2999' AS DATE FORMAT 'DD-MM-YYYY')) (NAMED BSNSS_EFFCTV_END_DT, TITLE 'Business Effective End Date')
This is the only way I have been able to assign both a new column name and a title. However, I understand NAMED is an older syntax which is being replaced by AS.
Unfortunately, I have been unable to rewrite this view definition using AS. I can assign the column name, but I cannot get the TITLE to work.
I have tried the following statements:
SELECT COALESCE(BSNSS_EFFCTV_END_DT, CAST('31-12-2999' AS DATE FORMAT 'DD-MM-YYYY')) (AS BSNSS_EFFCTV_END_DT, TITLE 'Business Effective End Date')
(Error: REPLACE VIEW Failed. 3706: Syntax error: expected something between '(' and the 'AS' keyword. )
SELECT COALESCE(BSNSS_EFFCTV_END_DT, CAST('31-12-2999' AS DATE FORMAT 'DD-MM-YYYY')) (TITLE 'Business Effective End Date') AS BSNSS_EFFCTV_END_DT
(No error on create, but the TITLE does not display, only the column name).
Can anyone help, even if it is just to point me to the correct reference.
We are using Teradata 13.10, and I am creating the View object using SQL Assistant 13.11. I have the SQL Assistant option to display TITLES rather than column names set ON.
Regards, and thanks in advance
The last version should be the correct one, (TITLE...) AS ..., and should return the title.
Seems to be a parser problem, you might contact TD support to get more details if this actually works as expected.
Otherwise you can easily keep the old NAMED, this syntax will probably never be removed :-)
When you do a SHOW QUALIFIED SELECT * FROM ODS_NMC_ADT_GRP_ADT_SCTN_MS_CV you get the internal source code and you will notice that it's rewriting every AS to NAMED :-)
Thanks Dieter. I will raise an issue with tech support, but at least have an initial solution.
I was worried that I was setting up a huge amount of rework for the medium term, using NAMED, but your last comment eases that concern. We can use what works, and gradually phase it out through natural attrition, once we get the (TITLE...) AS ..., form to function as expected.