All Forums General
Td12krish 11 posts Joined 10/10
06 Sep 2011
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

TDkrish
Jimm 298 posts Joined 09/07
06 Sep 2011

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.

Td12krish 11 posts Joined 10/10
07 Sep 2011

Thank you very much for the help:)

TDkrish

mjasrotia 66 posts Joined 08/11
09 Sep 2011

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

UdayaChow 6 posts Joined 07/11
10 Sep 2011

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.

UdayaChow

dnoeth 4628 posts Joined 11/04
14 Sep 2011

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

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

Dieter

Dieter

Td12krish 11 posts Joined 10/10
15 Sep 2011

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?

 

TDkrish

satishkumar206 3 posts Joined 10/11
12 Oct 2011

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

satishkumar206 3 posts Joined 10/11
12 Oct 2011

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

Adeel Chaudhry 773 posts Joined 04/08
13 Oct 2011

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

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.