Teradata equivalent of persisted computed column (in SQL Server)
- by Cade Roux
We have a few tables with persisted computed columns in SQL Server.
Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations?
The particular computed columns I am looking at conform some account numbers by removing leading zeros - an index is also created on this conformed account number:
ACCT_NUM_std AS ISNULL(CONVERT(varchar(39),
SUBSTRING(LTRIM(RTRIM([ACCT_NUM])),
PATINDEX('%[^0]%',
LTRIM(RTRIM([ACCT_NUM])) + '.'
),
LEN(LTRIM(RTRIM([ACCT_NUM])))
)
),
''
) PERSISTED
With the Teradata TRIM function, the trimming part would be a little simpler:
ACCT_NUM_std AS COALESCE(CAST(TRIM(LEADING '0' FROM TRIM(BOTH FROM ACCT_NUM))) AS varchar(39)),
''
)
I guess I could just make this a normal column and put the code to standardize the account numbers in all the processes which insert into the table. We did this to put the standardization code in one place.
