Creating a new column in Teradata using CASE statement

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Creating a new column in Teradata using CASE statement

I am trying to create a new column in a table in Teradata using a CASE Statement

I am trying a create a new column called ID_Number_Mod from a CASE Statement based off an original column called ID_Number from the table. Below is my code

alter table Table_A add (case when char_length(cast(cast(ID_Number as bigint) as varchar(50)))=12 then cast('999000' as char(6)) || cast(cast( substr(cast(cast(ID_Number as bigint) as char(12)), 4, 12) as bigint) as char(15)) else ID_Number end) as ID_Number_Mod float;

I am getting the below syntax error "expected something like a name or a Unicode delimited identifier between '(' and the 'case' keyword

2 REPLIES
Senior Apprentice

Re: Creating a new column in Teradata using CASE statement

Hi,

 

Nice try, but it's not going to work.

 

What you'll have to do is to create the new column with a 'simple' definition, i.e. FLOAT.

 

Then you'll need to run an update and in that command use your CASE expression.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Creating a new column in Teradata using CASE statement

Yes, Teradata doesn't support virtual / calculated columns.

 

To avoid the update, use a view.

Btw, there are too many conversions in your case, assuming Id_Number is a bigint :

with cte_data (ID_Number) as
(
select 56456142139999 (bigint) from cte_dual union all
select   124561421399 (bigint) from cte_dual
) , cte_dual (dummy) as (select 'X')
select ID_Number
     , case
         when char_length(cast(cast(ID_Number as bigint) as varchar(50)))=12
         then cast('999000' as char(6)) || cast(cast( substr(cast(cast(ID_Number as bigint) as char(12)), 4, 12) as bigint) as char(15))
         else ID_Number
       end (bigint) as ID_Number_Mod
     , case
         when ID_Number >= 1e11
          and ID_Number <  1e12
         then 999e12  + ID_Number - trunc(ID_Number, -9)
         else ID_Number
       end (bigint) as ID_Number_Mod2
  from cte_data;

ID_Number            ID_Number_Mod        ID_Number_Mod2
-------------------  -------------------  -------------------
 56 456 142 139 999   56 456 142 139 999   56 456 142 139 999
    124 561 421 399  999 000 561 421 399  999 000 561 421 399