答复: 答复: DDL column DEFAULT question

["Liu, Ming (Ming)" <mi...@esgyn.cn>]

Thanks Rohit, please help to file a JIRA, I think it will help a lot for database migration.

Thanks,
Ming

发件人: Rohit [mailto:rohit.jain@esgyn.com]
发送时间: 2016年3月15日 3:30
收件人: Liu, Ming (Ming) <mi...@esgyn.cn>; user@trafodion.incubator.apache.org
主题: RE: 答复: DDL column DEFAULT question

I ran into the same issue recently since other databases seem to allow this as there is no direct relationship / dependency between the NOT NULL specification and the DEFAULT specification. ANSI does specify them in a certain sequence but I am wondering if that is because one has to document them in a certain sequence anyway, or because it is intentional and they wanted implementations to follow that sequence.

I could have created a JIRA on this. But the way our parser works, this is not a modest change, and this is something that can easily be rectified in the DDL.

Rohit


-------- Original message --------
From: "Liu, Ming (Ming)" <mi...@esgyn.cn>>
Date: 03/14/2016 11:07 AM (GMT-06:00)
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: 答复: DDL column DEFAULT question
It works!

Thanks Anoop

发件人: anoop [mailto:anoop.sharma@esgyn.com]
发送时间: 2016年3月15日 0:05
收件人: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
主题: RE: DDL column DEFAULT question

default date not null...

default comes before null clause. that
is ansi definition


anoop
-------- Original message --------
From: "Liu, Ming (Ming)" <mi...@esgyn.cn>>
Date: 3/14/2016 9:01 AM (GMT-08:00)
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: DDL column DEFAULT question

Hi, all,

I need to create a table in Trafodion with columns having DEFAULT, but I cannot make it work, any help will be very appreciated:

CREATE TABLE DBMARTA.ARPT_DIM_AUTORPT_PUB_MARK
(MARK_ID     SMALLINT        NOT NULL,
  BEGIN_TIME  DATE            NOT NULL  DEFAULT date'2008-01-01',
  END_TIME    DATE            NOT NULL  DEFAULT date'2018-01-01',
  ACTIVE_FLAG SMALLINT,
  MARK_NAME   VARCHAR(20),
  DESC_TXT    VARCHAR(80),
  primary key(MARK_ID, BEGIN_TIME, END_TIME)
);
*** ERROR[15001] A syntax error occurred at or before:
CREATE TABLE DBMARTA.ARPT_DIM_AUTORPT_PUB_MARK  (MARK_ID     SMALLINT        NO
T NULL,   BEGIN_TIME  DATE            NOT NULL  DEFAULT date'2008-01-01',   END
                                                      ^ (134 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

It seems NOT NULL and DEFAULT are conflicting? Or my syntax has some other issue?

Thanks,
Ming