Archives of the TeradataForum

Message Posted: Fri, 27 Sep 2002 @ 15:45:27 GMT

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

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 .

Sorry if I haven't explained it very well but its approaching the end of a long Friday and I am goosed, hopefully the SQL below might clarify things a little.

Thanks

Howard.

create table T_TEST
(
INDEXCOLUMN INTEGER NOT NULL
,DATECOLUMN DATE FORMAT'YYYYMMDD' NOT NULL
)
PRIMARY INDEX(INDEXCOLUMN);

CREATE VIEW V_TEST
 (
 INDEXCOLUMN
,DATECOLUMN
 )
 AS LOCKING T_TEST FOR ACCESS
 SELECT
 INDEXCOLUMN
,DATECOLUMN
 FROM T_TEST;

CREATE TABLE T_TEST_T
AS
(SEL * FROM T_TEST)
WITH DATA
PRIMARY INDEX(INDEXCOLUMN);

CREATE TABLE T_TEST_V
AS
(SEL * FROM V_TEST)
WITH DATA
PRIMARY INDEX(INDEXCOLUMN);

SHOW TABLE T_TEST_T;

resulting answer set
CREATE SET TABLE SUPPDB.T_TEST_T ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      INDEXCOLUMN INTEGER NOT NULL,
      DATECOLUMN DATE FORMAT 'YYYYMMDD' NOT NULL)
PRIMARY INDEX ( INDEXCOLUMN );

SHOW TABLE T_TEST_V;
resulting answer set
CREATE SET TABLE SUPPDB.T_TEST_V ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      INDEXCOLUMN INTEGER,
      DATECOLUMN DATE FORMAT 'yyyy/mm/dd')
PRIMARY INDEX ( INDEXCOLUMN );

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