All Forums Database
anurag2117 2 posts Joined 06/16
07 Jun 2016
Date format automatically changed

Hi,
We have a view (below is the ddl) (view name is a)

REPLACE VIEW test.a  AS LOCK ROW FOR ACCESS 

SELECT * FROM test.b;

 

and below is the ddl fro table b (from where view is created)

 

CREATE MULTISET VOLATILE TABLE test.b,NO FALLBACK ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO,

     LOG

     (

      MIN_TIME_ID DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( MIN_TIME_ID )

 

now i am trying to create a volatile table as-

 

create volatile table c as (select * from test.a) with no data on commit preserve rows;

 

table is getting created successfully, but date format is automatically changed.

 

show table test.c --(c is volatile table)

 

CREATE MULTISET VOLATILE TABLE NACO_RADAR_PROD.abc1 ,NO FALLBACK ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO,

     LOG

     (

      MIN_TIME_ID DATE FORMAT 'YY/MM/DD')

PRIMARY INDEX ( MIN_TIME_ID )

ON COMMIT PRESERVE ROWS;

 
 
Please help me, why this is happening.

dnoeth 4628 posts Joined 11/04
08 Jun 2016

When a table is created using CREATE TABLE AS SELECT all options revert back to default, e.g. all columns are NULLable, all indices are lost.
 
In your case you can do a SET SESSION DATEFORM = ANSIDATE before or change you user's default MODIFY USER xxx AS  DATEFORM = ANSIDATE

Dieter

anurag2117 2 posts Joined 06/16
09 Jun 2016

Thank you so much.
It worked like charm :)

You must sign in to leave a comment.