 |
 |
Archives of the TeradataForum
Message Posted: Fri, 27 Sep 2002 @ 16:09:31 GMT
| Subj: | | Re: CREATE TABLE AS from views |
| |
| From: | | Geoffrey Rommel |
| | When creating tables using the CREATE TABLE AS method combined with a subquery which references a view rather than a table I am
finding that columns with data type DATE are defaulting to FORMAT 'yyyy/mm/dd' irrespective of the format of the underlying table. I have
tried explicitly putting a format within the view and formatting as part of the select in the subquery but all to no avail. | |
| | Is this expected behaviour and if so is there any way of changing this default setting? | |
This is not the only oddity of CREATE TABLE AS. I just discovered that case expressions that return character strings default to
character set Unicode rather than Latin -- so our users are suddenly creating tables with Unicode columns, which take twice as much space as
ordinary characters.
Now to your question. I believe this behavio[u]r is reasonable. When you select a date column, it becomes part of a result set.
Strictly speaking, columns in result sets do not have formats; they have data types only. If you are using the default dateform=integer,
date columns are integers, not character strings. The fact that BTEQ, for instance, uses the default format when displaying results means
only that the writers of BTEQ have been conscientious enough to do so. Queryman, as you may have noticed, uses its own formats. You can
think of "create table as (select ...)" in this way: (1) the select statement is executed; (2) the data types of the resulting columns, but
not their formats, are determined [this would be similar to reading an SQLDA]; (3) the target table is created with those data types.
For the same reason, it is possible to force the data type you want by explicitly casting within your select statement, but I don't think
that is possible for formats.
--wgr
| |
