on 03-26-2015 7:47 PM
Discovered a syntax issue with the DDL when specifying a default value for a timestamp. The DDL needs to look like the following:
DEFAULT TIMESTAMP '1900-01-01 00:00:00.000000'
But PowerDesigner will only specify DEFAULT '1900-01-01 00:00:00.000000'
I tried adding the TIMESTAMP keyword as part of the value, but of course PowerDesigner doesn't know what to do with that and puts quotes around it. My other thought is to customize the XDB (which I already have quite a bit given we are on Teradata v15) to put a condition to change the DDL if the data type is a date, but I cannot seem to get the syntax right. I am using %DATATYPE%='TIMESTAMP(6)'? for the condition. No variation of this in modifying the column syntax to check for a condition works so that the code will look like the following if the condition is met:
[.Z:[%WITHDEFAULT%? with
default[ %DEFAULT%]:[ DEFAULT TIMESTAMP %DEFAULT%]]
Would very much appreciate any assistance or pointers in getting this resolved.
For the syntax that you desire, what would the complete create table statement look like?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the reply. Here is an example:
create multiset table ADS_BKG.PKG_TYP
,no fallback
,no before journal
,no after journal
(PKG_TYP_ID SMALLINT not null,
PKG_TYP_NAME VARCHAR(200) not null,
ETL_LOAD_TAG INTEGER,
ETL_CREATE_DATETM TIMESTAMP(6) not null
DEFAULT TIMESTAMP '1900-01-01 00:00:00.000000',
ETL_UPDATE_DATETM TIMESTAMP(6) not null
DEFAULT TIMESTAMP '1900-01-01 00:00:00.000000',
ETL_CREATE_PROCESS_RUN_ID INTEGER not null
DEFAULT 0,
ETL_UPDATE_PROCESS_RUN_ID INTEGER not null
DEFAULT 0
)
unique primary index UPI_PKG_TYP (PKG_TYP_ID);
Hi Monique,
You should enter the TIMESTAMP keyword inside your default value.
To avoid enquoting it upon generation, enclose the whole default value with tildes (~)
ex.
On column property sheet, enter default value as ~TIMESTAMP '1900-01-01'~
Your SQL will then looks like what you expect
<<
create table TABLE_1 (
COLUMN_1 TIMESTAMP
default TIMESTAMP '1900-01-01'
)
no primary index;
>>
Marc
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.