All Forums Teradata Applications
06 Feb 2014
Bad character in format or data

Hi All,
Not able to figure about the below cause for error. The error is relating to casting. But when i tried cast f12 while inserting the data still i am getting the same error. Please help me to solve this issue.
.logon home/jbhatt,jugal
 .import vartext ',' file=/home/jbhatt/insert.txt
 .quiet on
 .repeat*
 using
 f1 (VARCHAR(40)),
 f2 (VARCHAR(40)),
 f3 (CHAR(40)),
 f4 (CHAR(4)),
 f5 (VARCHAR(40)),
 f6 (VARCHAR(40)),
 f7 (VARCHAR(40)),
 f8 (VARCHAR(40)),
 f9 (VARCHAR(40)),
 f10 (VARCHAR(40)),
 f11 (VARCHAR(40)),
 f12 (VARCHAR(40)),
 f13 (VARCHAR(40)),
 f14 (VARCHAR(40)),
 f15 (VARCHAR(40)),
 f16 (VARCHAR(40)),
 f17 (VARCHAR(40)),
 f18 (VARCHAR(40))
 insert into dbjugal.tbjugal values(:f1,:f2,:f3,:f4,:f5,:f6,:f7,:f8,:f9,:f10,:f11,: f12,:f13,:f14,:f15,:f16,:f17,:f18);
 .logoff
 .quit
 
 
  *** Starting Row 0 at Thu Feb  6 15:14:27 2014
 *** Failure 2621 Bad character in format or data of tbjugal.f12.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 *** Warning: Out of data.
 *** Finished at input row 1 at Thu Feb  6 15:14:27 2014
 *** Total number of statements: 1,  Accepted : 0,  Rejected : 1
 *** Total elapsed time was 1 second.
 BTEQ -- Enter your SQL request or BTEQ command:
 *** Warning: EOF on INPUT stream.
.logoff
 
 
 Data i am importing is from a text file(insert.txt). Given below:

 5461,1000,null,XXX,3020,12,198.59,1,1,0,198.59,null,nu ll,null,null,978,998,999
 
 
 
 CREATE MULTISET TABLE dbjugal.tbjugal ,NO FALLBACK ,
      NO BEFORE JOURNAL,
      NO AFTER JOURNAL,
      CHECKSUM = DEFAULT,
      DEFAULT MERGEBLOCKRATIO
      (
       f1 BIGINT NOT NULL,
       f2 INTEGER NOT NULL,
       f3 CHAR(40) CHARACTER,
       f4 CHAR(4) CHARACTER ,
       f5 INTEGER NOT NULL ,
       f6 BYTEINT ,
       f7 DECIMAL(15,2) COMPRESS(198.59),
       f8 INTEGER ,
       f9 INTEGER ,
       f10 INTEGER ,
       f11 DECIMAL(15,2) COMPRESS (198.59),
       f12 DECIMAL(15,2) COMPRESS
       f13 INTEGER ,
       f14 INTEGER ,
       f15 INTEGER ,
       f16 INTEGER ,
       f17 INTEGER ,
       f18 INTEGER
 PRIMARY INDEX (f1,f2,f3);

dnoeth 4628 posts Joined 11/04
09 Feb 2014

When you try to cast the string 'null' to a Decimal, of course this fails.
You have to add NULLIF to all numeric columns: NULLIF(:f12, 'null')

Dieter

10 Feb 2014

Thanks Dnoeth.  :)

 

 

You must sign in to leave a comment.