ORACLE TO TERADATA MIGRATION

General
Enthusiast

ORACLE TO TERADATA MIGRATION

Hi ALL,

While migrating tables from oracle to teradata I came across few table which have teradata keywords as column names. Please let me know how to go ahead furhter

CREATE TABLE xyz

(

      CSTMR_ID              NUMERIC  ,

      CSTMR_CNTCT_STTS_NAME  VARCHAR(35)  ,

      TITLE                 VARCHAR(15)  ,

      GIVEN_NAME            VARCHAR(200)  ,

      FAMILY_NAME           VARCHAR(100)  ,

      DOB                   DATE  ,

      EMAIL                 VARCHAR(400)  ,

      ID, CSTMR_CNTCT_ROLE_NAME);

And also while converting one of the table throws error as "number of leading digits out of reange" and table is as shown below

CREATE TABLE DELTA_SLA_LOAD_LOG

(

 PROCESS               VARCHAR(30)  ,

 FILE_TIMESTAMP        DATE  ,

 START_DATETIME        DATE  ,

 END_DATETIME          DATE  ,

 DURATION              INTERVAL DAY(9) TO SECOND(9)  ,

 STATUS                VARCHAR(30)  ,

 DA_CREATE_DATE        DATE 

)

;

 Can anyone let me konw the syntax for the same in teradata? your help is appreciated

9 REPLIES
Enthusiast

Re: ORACLE TO TERADATA MIGRATION

Put the keyword in double quotes.

So:


CREATE TABLE xyz


(


      CSTMR_ID              NUMERIC  ,

      CSTMR_CNTCT_STTS_NAME  VARCHAR(35)  ,

      "TITLE"                 VARCHAR(15)  ,

      GIVEN_NAME            VARCHAR(200)  ,

      FAMILY_NAME           VARCHAR(100)  ,

      DOB                   DATE  ,

      EMAIL                 VARCHAR(400)  ,

      ID, CSTMR_CNTCT_ROLE_NAME);


And also while converting one of the table throws error as "number of leading digits out of reange" and table is as shown below


CREATE TABLE DELTA_SLA_LOAD_LOG

(

 PROCESS               VARCHAR(30)  ,

 FILE_TIMESTAMP        DATE  ,

 START_DATETIME        DATE  ,

 END_DATETIME          DATE  ,

 "DURATION"              INTERVAL DAY(9) TO SECOND(9)  ,

 STATUS                VARCHAR(30)  ,

 DA_CREATE_DATE        DATE 

)

;


Note that your SQL will have to refer to "Title", "Duration" - not Title or Duration.


Interval Day To Second has a maximum precision of Day(4) to Second(6). See SQL Reference, Date/ Time and Interval Data Types manual.

Enthusiast

Re: ORACLE TO TERADATA MIGRATION

Thank you very much for the help:)

Enthusiast

Re: ORACLE TO TERADATA MIGRATION

I would still advocate for not keeping column names as keywords. You may decide to follow a standard to rename those columns.

e.g TITLE would go as <Proj_Code>_TITLE

PC_TITLE

Thanks

Manik

Enthusiast

Re: ORACLE TO TERADATA MIGRATION

Hi,

when creating


CREATE TABLE DELTA_SLA_LOAD_LOG

(

 PROCESS               VARCHAR(30)  ,

 FILE_TIMESTAMP        DATE  ,

 START_DATETIME        DATE  ,

 END_DATETIME          DATE  ,

 "DURATION"              INTERVAL DAY(9) TO SECOND(9)  ,

 STATUS                VARCHAR(30)  ,

 DA_CREATE_DATE        DATE );


this table in SQLA it shows the following error


" number of leading digits out of range "


pls clarify me about this error.

Junior Contributor

Re: ORACLE TO TERADATA MIGRATION

You have to apply the cast/format in your select:

,:FIRST_CREATE_DATE (timestamp(0), format 'dd-mm-yyyy:hh:mi:ss')

Dieter
Enthusiast

Re: ORACLE TO TERADATA MIGRATION

Hello Dieter,

Superrrr...that worked thanks for the expert advice however I got another issue

My source is 7/10/2001  and

DDL is UPDATE_DATE DATE FORMAT 'yyyy-mm-dd'

via  fastload is there any way to load the data to the target in the format as shown in the ddl?

I have tried to use SED command in the source and converted to 7-10-2001 and in fastload

i have used : values (

:UPDATE_DATE ( format 'dd-mm-yyyy')  ); stilll

all the values goes to error values not sure y?

Re: ORACLE TO TERADATA MIGRATION

my project is migrating 2 sqlserver 2 teradata

i have one reqirement i have total 20 tabels

create procedure total-count

as

begin

insert into total_count_tab('count of table','select count(*) from tablenamea)

insert into total_count_tab('count of table','select count(*) from tablenameb)

insert into total_count_tab('count of table','select count(*) from tablenamec)

insert into total_count_tab('count of table','select count(*) from tablenamed)

end

i implemented in sql

in teradata how to impliment this

Re: ORACLE TO TERADATA MIGRATION

when run a query i have total 20 tabels count in one table that table colums are sno,discription,count

Teradata Employee

Re: ORACLE TO TERADATA MIGRATION

Hi,

For new question please use new thread!

You can simply use following to do that:


CREATE PROCEDURE TotalCount()


BEGIN


DECLARE varCount BIGINT;


SELECT COUNT(*) INTO :varCount FROM Table1;


INSERT TOTAL_COUNT_TABLE


VALUES (1, 'Count of Table2', :varCount);


SELECT COUNT(*) INTO :varCount FROM Table2;


INSERT TOTAL_COUNT_TABLE


VALUES (2, 'Count of Table2', :varCount);



END;


HTH!


Regards, Adeel