Strange Error in Time(n)

UDA
Enthusiast

Strange Error in Time(n)

Can anyone tell me whats the problem with the below query?

Create volatile table A
(
col_a TIME(6)
) on commit preserve rows;

create volatile table B
(
col_b TIME(6)
) on commit preserve rows;

delete from A;

insert into A (col_a)
sel
null as col_a
from B;
4 REPLIES
Enthusiast

Re: Strange Error in Time(n)

insert into A (col_a)
sel
null as col_a
from B;
*** Failure 5407 Invalid operation on an ANSI Datetime or Interval value.

The problem is that, in standard SQL, NULL is not a general value expression. NULL is a special syntactical element that is allowed only in certain specified contexts. The correct solution is to use CAST, like this:

insert into A (col_a) sel cast(null as time(6)) from b;
*** Insert completed. No rows added.
Enthusiast

Re: Strange Error in Time(n)

I should have added that Teradata, as an extension to standard SQL, allows NULL in the context of a value expression, with the default type of Integer.

select type(null);

Type(Null)
---------------------------------------
INTEGER

However, the CAST(NULL AS ) syntax is explicitly blessed as a valid general value expression in the SQL standard.
Enthusiast

Re: Strange Error in Time(n)

Now,
Can you tell me why the following query works without a CAST?

Create volatile table A
(
col_a TIME(6)
) on commit preserve rows;

create volatile table B
(
col_b TIME(6)
) on commit preserve rows;

delete from A;

insert into A (col_a)
sel
null as col_a
from B;

Enthusiast

Re: Strange Error in Time(n)

The insert values list is one of the places where NULL is explicitly allowed, according to the SQL standard.