error numeric overflow when casting as char

Database
Enthusiast

error numeric overflow when casting as char

Hi,

I am trying to use the SUBSTR function to combine a number field with the first 3 digits of that same number field so that the first 3 digits are repeated.  I have no problem using the following code...

SUBSTR(d.ID_NO, 1, 3) + d.ID_NO

 I am struggling now to cast that so that the ouput is a text field vs a numeric... I tried the following but am getting the numeric overflow error.

 

 CAST(CAST(SUBSTR(d.ID_NO, 1, 3)+(d.ID_NO) AS INTEGER) AS CHAR(1)) 

 Any suggestions?

 

Thanks!

3 REPLIES 3
Teradata Employee

Re: error numeric overflow when casting as char

Hi nbwest76,

 

If you could provide some input data and expected output it would be helpful.

I can't see the link between your need and the code so far.

 

The error is because substr(something, 1, 3) returns 3 characters, and later you try to cast that into 1 character, so it just doesn't fit.

Enthusiast

Re: error numeric overflow when casting as char

Hi Waldar,

That makes sense. An example of the numeric ID_NO field looks something like this '68012345678912'.  I'm trying to duplicate the first 3 digits so that it looks like '68068012345678912' and becomes 17 digits.  I would also like the field output to to convert from number to text.

 

thank you!

Highlighted
Teradata Employee

Re: error numeric overflow when casting as char

Ok, note the concatenation operator is || in SQL, I think only SQL-Server and Sybase use + as a concatenation operator.

 

Try this code :

create multiset volatile table mvt_tab, no log
( mvt_pi    byteint
, col_num   bigint
)
primary index (mvt_pi)
on commit preserve rows;

insert into mvt_tab values (0, 68012345678912);

select col_num
     , cast(col_num as varchar(14)) as col_chr
     , substring(col_chr from 1 for 3) || col_chr as col_chr_concat
     , type(col_chr_concat)
  from mvt_tab
 where mvt_pi = 0;