Seconds_between doesn't work with variables in SQLScript

[Former Member]

There is a sample procedure:

create procedure testproc
language SQLSCRIPT
as
l_curtime timestamp;
l_timedelta integer;
begin
  select systimestamp into l_curtime from dual;
  select seconds_between(:l_curtime, systimestamp) into l_timedelta from dual;
end;

It should return seconds between 2 timestamp values.

The code returns an error:

Could not execute 'create procedure testproc language SQLSCRIPT as l_curtime timestamp; l_timedelta integer; begin ...' SAP DBTech JDBC: (at 160): inconsistent datatype: line 8 col 10 (at pos 160)

With «time» and «date» datatypes that's the same. So, seconds_between doesn't work with SQLScript variable (in this example, l_curtime). Is it a normal behaviour or supposed to be a bug?

[Norman_May]

Hi,

could you please use

create procedure testproc

language SQLSCRIPT

as

l_curtime timestamp;

l_timedelta integer;

begin

select systimestamp into l_curtime from dual;

select seconds_between(to_date(:l_curtime), to_date(systimestamp)) into l_timedelta from dummy;

end;

until the bug fix is generally available.

Kind regards, Norman

[Former Member]

Thanks, Norman!

With to_timestamp it also works and gives the result with good precision, not rounded to 12:00:

create procedure dima_hdb_tools.testproc
language SQLSCRIPT
as
l_curtime timestamp;
l_timedelta integer;
begin
select systimestamp into l_curtime from dual;
select seconds_between(to_timestamp(:l_curtime), to_timestamp(systimestamp)) into l_timedelta from dummy;
end;

[tomas-krojzl]

Hello,

I can confirm same behavior - using variable inside seconds_between function leads to error. If you put there static string it is ok.

Of course you can always create "dummy" table (having 1 row with 1 column) where you will store the result of first call and then you will reference in second. But of course it is not that clean and it will be slower (especially if called many times).

But I would suggest to wait few more days - there should be SP03 released and everything can be different.

Tomas