TPT - Oracle Date to Teradata Timestamp(0)

Tools
Enthusiast

TPT - Oracle Date to Teradata Timestamp(0)

I just upgraded from ttu8.1 to ttu8.2. I am loading a Teradata table via the TPT wizard from an Oracle table via fastload (load). When selecting the source table and fields, the tpt wizard does not know how to define the Oracle date field and requires me to edit the definition from a dropdown list. Has anyone else experienced this?

Every combination that I have used from the dropdown list has failed when trying to load to a Teradata timestamp(0) field. Has anyone else had any success moving data from and Oracle DATE to Teradata TIMESTAMP(0)? If so, a sample script would be greatly appreciated! I suspect it might be an ODBC problem. I am selecting the 'Oracle Tables' option from the source versus the 'ODBC DSN' option. The 'ODBC DSN' option always me to see the source tables in the schema I select, but fails when I try to access the fields in any table.

Thanks in advance for any input you may provide,

jgleaso
5 REPLIES
Enthusiast

Re: TPT - Oracle Date to Teradata Timestamp(0)

jgleaso,

Things that worked for me.
In oracle if INTDATE then in Teradata it was DATE
In Oracle if TIMESTAMP then in Teradata i tried with CHAR(19)

May be you could try some thing like this

SelectStmt = 'select DATE||''00:00:00'' as timestp from abc ';

Just a thought.. Let me know if it worked for you.

And also let me know if you ever worked with Column names that are reserved in Teradata but not in Oracle like 'Title'.

Srikant
Enthusiast

Re: TPT - Oracle Date to Teradata Timestamp(0)

This is in directly from Teradata:

When sourcing an Oracle DATE to be loaded into a date field in a Teradata table, the following has to be done:

Oracle Teradata Source Definition Select Statement Format
DATE TIMESTAMP(0) Varchar(19) to_char(inputDateField,''yyyy/mm/dd:hh24:mi:ss'')
DATE TIME Varchar(15) to_char(inputDateField,''hh24:mi:ss'')
DATE DATE Varchar(10) to_char(inputDateField,''yyyy/mm/dd'')

Use two single quotes instead of a quotation mark.

I have verified that this works...

Enthusiast

Re: TPT - Oracle Date to Teradata Timestamp(0)

jgleaso,
Can you post your Selectsmt please
Enthusiast

Re: TPT - Oracle Date to Teradata Timestamp(0)

SelectStmt = 'SELECT CHANNEL_CODE,CHANNEL_ABBR,CHANNEL_NAME,CTRL_DATA_LOP_ID,to_charCTRL_DATA_LOP_DT,''yyyy/mm/dd:hh24:mi:ss'') FROM REFWORK.CHANNEL;',

Re: TPT - Oracle Date to Teradata Timestamp(0)

Thank you so much JGLEASO; this works for me.

TO_CHAR(CREATEDT, 'YYYY/MM/DD:HH24:MI:SS')