Teradata Error 5407 Invalid operation for DateTime or Interval

Question

I am trying to an Ins from a bigint col to a bigint col and I get this error

Error 5407 Invalid operation for DateTime or Interval.

here is the query pattern

    INSERT  INTO sanboxdb.Mtb     (  colA < offending column is BIGINT > ,
        <other non offensive col-list> ) 

SELECT    
( TRIM(Ptb.Colx) ||
TRIM(Coly)||
TRIM(ROW_NUMBER() OVER (PARTITION BY Xtb.ColZ   
ORDER   BY ColP,ColQ  )  +

COALESCE(SUBSTR(CAST(Mtb.ColA_BigIntCol   AS CHAR (20)),
        6),0) )  ) ( BIGINT)   AS colA  /* , TYPE  ( colA ). I verified that type is indeed BIGINT */,  


        <rest of the query Logic here.All the rest of the col checkout fine>'

I can do this

`Ins into ( BigintCol) sel '1123343434'` <br> 

and it wont complain !

But If I do this

ins into DB.TB  ( Bigintcol ) sel  substring ( bigintcol,6,0) from DB.TB sample 5 

or in fact this

 ins into DB.TB  ( Bigintcol ) sel  substring ( bigintcol,6,0) ( bigint) from DB.TB sample 5 

It does not like this . You can ignore this component

( TRIM(Ptb.Colx) ||
TRIM(Coly)||
TRIM(ROW_NUMBER() OVER (PARTITION BY Xtb.ColZ   
ORDER   BY ColP,ColQ  )  +

I included it just to show the actual query outline but the offensive component is this part

  COALESCE(SUBSTR(CAST(Mtb.ColA_BigIntCol   AS CHAR (20)),
            6),0) )  )

None of these datatypes are Interval or datetime. In fact it's the same bigint datatype. Why then does it throw the error like that .

Answer 1

I faced no issue when I simulate scenario like you, is it possible for you to give sample values for bigintcol? or try to construct similar scenario like me and debug.

/* create table with bigint column */
create table test3
( a bigint)
primary index(a);

/* inserted bigint value */
insert into test3 values(9223372036854775807);

/* selected bigint as character and operation you want to perform */
select cast(a as char(19)),  COALESCE(SUBSTR(CAST(a   AS CHAR (20)),6),0) bi
from test3;

/* inserted char value in bigint column*/
insert into test3
select  COALESCE(SUBSTR(CAST(a   AS CHAR (20)),6),0) bi
from test3;