cancel
Showing results for 
Search instead for 
Did you mean: 

Default Timestamp DDL Issue for Teradata

former_member1144245
Participant
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

former_member192453
Active Participant
0 Kudos

For the syntax that you desire, what would the complete create table statement look like?

former_member1144245
Participant
0 Kudos

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);

marc_ledier
Active Participant
0 Kudos

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

former_member1144245
Participant
0 Kudos

Hi Marc,

Sorry was offline for a few days. That worked perfectly! Thanks so much for your help.

~ Monique

Answers (0)