Creating a new column in Teradata using CASE statement

Question

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

Answer 1

Try changing your query to

alter table Table_A
    add ID_Number_Mod as (
        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);