Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 27 Sep 2002 @ 16:09:31 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023