Archives of the TeradataForum

Message Posted: Fri, 24 Apr 2015 @ 12:26:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Friday, April 24, 2015 06:21 -->

Thank you everyone.

All your solutions works well in SQLA/BTEQ but it is creating problem in the stored procedure.

Below is the sample which I used for testing

     --create volatile table
     create multiset volatile table vt_date_time (
     timestamp1 timestamp(0)
     )
     primary index(timestamp1)
     on commit preserve rows;

     --insert the current timestamp
     insert into vt_date_time
     select current_timestamp(0) ;

     --calculation
     select
     (current_timestamp(0) - timestamp1) hour to second(0) as time_1, (( (extract(hour   from (current_timestamp(0) - timestamp1 hour tosecond(2))) * 3600)
     + (extract(minute from (current_timestamp(0) - timestamp1 hour to second(2))) * 60)
     +  extract(second from (current_timestamp(0) - timestamp1 hour to second(2))) )/60   ) as time_2,
     ((current_timestamp(0) - timestamp1 hour to second)) as time_3,
       ((current_timestamp(0) - timestamp1 hour(4) to second(2))) as time_4 from vt_date_time;

These solution throws Missing/Invalid SQL statement'E(5407):Invalid operation for DateTime or Interval.'. in stored procedure but works well in SQLA/BTEQ

Any pointer handling it in stored procedure?

Thanks again!!

	<Prev	Next>		<<First	<Prev	Next>	Last>>