Teradata equivalent of persisted computed column (in SQL Server)

Posted by Cade Roux on Stack Overflow See other posts from Stack Overflow or by Cade Roux
Published on 2010-03-08T18:05:41Z Indexed on 2010/03/19 17:21 UTC
Read the original article Hit count: 370

Filed under:

sql-server

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.

Developer IT