Dynamic Column Name

Database
Enthusiast

Dynamic Column Name

How can I create a view with Dynamic column name ?

column name is dependent on the current_date

something of this sort

CREATE VIEW TEMP_V

SELECT ID,ARTICLE,

CASE YEAR_MONTH

WHEN CAST(CAST(CAST(DATE AS FORMAT 'YYYYMM')AS CHAR(6))AS INT)

THEN AMOUNT

END AS 'REVENUE-'||CAST(CAST(DATE AS FORMAT 'YYYYMM')AS CHAR(6))

FROM SALES_HIST

6 REPLIES
Enthusiast

Re: Dynamic Column Name

In the query mentioned above TD is not supporting Dynamic column.

END AS 'REVENUE-'||CAST(CAST(DATE AS FORMAT 'YYYYMM')AS CHAR(6))

Its giving an error saying :

SELECT Failed, 3707; Syntax error, expected something like name or a Unicode delimited identifier between the 'AS' keyword and the string 'R' keyword.

Junior Contributor

Re: Dynamic Column Name

Names must be enclosed in double quotes, " instead of ', but it's still not working, there are no dynamic names in Teradata.

Why do you need that dynamic name just for output?

Can't you simply use REVENUE_CURRENT_MONTH?

Or run a monthly job to REPLACE that view with the current value?

Dieter

Enthusiast

Re: Dynamic Column Name

Thanks Dieter

actually I am looking for a view that calculates Revenue for the last 2 years.

so i need coulmns in this fashion

Revenue-201304

Revenue-201303

Revenue-201302

Revenue-201301

so on for last 2 years

Junior Contributor

Re: Dynamic Column Name

There are no dynamic column names unless you REPLACE the view.

And you should do a SUM(CASE...) to get all the different months in a single row.

Dieter

Enthusiast

Re: Dynamic Column Name

Thanks again Dieter

I am done with the VIEW.

I had to use a stored procedure which replaces the VIEW. The only drawback is, to get the report I need to first call the stored Procedure and then select the view

Enthusiast

Re: Dynamic Column Name

REPLACE PROCEDURE SP_TEMP ()

CALL DBC.SYSEXECSL(

'REPLACE VIEW TEMP_V

AS

SELECT ID,ARTICLE,

CASE YEAR_MONTH

WHEN CAST(CAST(CAST(DATE AS FORMAT 'YYYYMM')AS CHAR(6))AS INT)

THEN AMOUNT

ELSE 0

END AS "REVENUE-'||CAST(CAST(DATE AS FORMAT 'YYYYMM')AS CHAR(6))||'",

CASE YEAR_MONTH

WHEN CAST(CAST(CAST(ADD_MONTHS(DATE,-1) AS FORMAT 'YYYYMM')AS CHAR(6))AS INT)

THEN AMOUNT

ELSE 0

END AS "REVENUE-'||CAST(CAST(ADD_MONTHS(DATE,-1) AS FORMAT 'YYYYMM')AS CHAR(6))||'"

FROM SALES_HIST');

CALL SP_TEMP ();

SELECT * FROM TEMP_V;