As far as I know, Teradata would have as base to create the new table the source information, as it was defined originally. So, if the column "COMPLAINT_REFERENCE_NUMBER" was originally created as CHAR(5), your new table (TEMP) should be created with a column having the same data type (CHAR(5)), even if you do not mention a data type during the table creation.
I just would like to call your attention to one point: you may create a table using "CREATE NEW_TABLE AS (SELECT ... )" or "CREATE NEW_TABLE AS OLD_TABLE" statements. The difference between both regards in the Primary Index definition, where the first statement would create the primary index using the first column in the source table and the second statement would create the NEW_TABLE with the same definition of OLD_TABLE, including the Primary Index.
Thanks for the reply, I generally don't create my tables using CREATE TABLE AS, as I consider it bad practice (thats just my thoughts!)
I suppose my question was asked because its part of a bigger picture I am trying to create.
I'd like to create generated SQL which derives a table definition as the output.
so for example,
TABLE A ( X INTEGER, Y VARCHAR(10), X DATE )
but I would like to create a table with only X and Y, and my goal would be to create some generated sql that could look at table A's column definitions for X and Y and produce the output of a table DDL
So have my generated sql
which will produce an output of,
(obviously this would be in correct teradata sql)
TABLE A ( X INTEGER, Y VARCHAR(10) )
so this would also work if I only wanted to generate one column or six columns etc....