Bad character in format or data

Teradata Applications
Enthusiast

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

2 REPLIES
Junior Contributor

Re: Bad character in format or data

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

Enthusiast

Re: Bad character in format or data

Thanks Dnoeth.  :)