Parameters in Procedures + Time Travel

[fabian_krger]

Hey guys it seems like the timestamp for Time-Travel (History Table) can not be set by a parameter?

(Simplified Example)

1) When i try

CREATE PROCEDURE "SYSTEM"."sql_select_with_param" (IN param VARCHAR(50)) LANGUAGE SQLSCRIPT AS
BEGIN
SELECT DISTINCT VBAP.PSTYV AS VBAP_PSTYV, COUNT(*) AS XCOUNT 
FROM VBAP 
WHERE VBAP.PSTYV = :param 
GROUP BY VBAP.PSTYV;
END;
CALL "sql_select_with_param"('TAN');

it works like a charm:

	VBAP_PSTYV	XCOUNT
1	TAN	3482

2) Time Travel is working, too:

CREATE PROCEDURE "SYSTEM"."sql_select_with_param" (IN param VARCHAR(50)) LANGUAGE SQLSCRIPT AS
BEGIN
SELECT DISTINCT VBAP.PSTYV AS VBAP_PSTYV, COUNT(*) AS XCOUNT FROM 
VBAP GROUP BY VBAP.PSTYV 
AS OF UTCTIMESTAMP '2012-07-26 12:48:56';
END;
CALL "sql_select_with_param"('');

gives me all data as of July 26:

	VBAP_PSTYV	XCOUNT
1	TAN	3482
2	KLN	2
3	AFN	1
4	AGN	12
5	LPN	4303
6	LZN	1
7	KMN	2
8	WVN	1
9	REN	12
10	G2N	2

3) but when i try to use the parameter as timestamp for the time-travel:

CREATE PROCEDURE "SYSTEM"."sql_select_with_param" (IN param VARCHAR(50)) LANGUAGE SQLSCRIPT AS
BEGIN
SELECT DISTINCT VBAP.PSTYV AS VBAP_PSTYV, COUNT(*) AS XCOUNT 
FROM VBAP GROUP BY VBAP.PSTYV 
AS OF UTCTIMESTAMP :param;
END;
CALL "sql_select_with_param"('2012-07-26 12:48:56');

I receive the following error:

Could not execute 'CALL "sql_select_with_param"('2012-07-26 12:48:56')'
SAP DBTech JDBC: [257]: sql syntax error:  [257] SYSTEM.sql_select_with_param: line 4 col 1 (at pos 105): SQLException257 exception: sql syntax error: incorrect syntax near "-": line 1 col 124 (at pos 124)

Currently I am on Revision 29.

I tried using dynamic SQL but it seems like it is not made for select-statements.

Anybody knows help?

Kind Regards,

Fabian

[fabian_krger · Accepted solution]

Hello Guys,

thanks for all of your feedback.

As Henrique mentioned it was not a cast error or something.

Today I had the possibility to test the procedure on a HANA Revision 38 and now it is working. Seems like it was a bug in Revision 29...

Kind Regards,

Fabian

[henrique_pinto · Accepted solution]

That's great news.

Since (I suppose) you got your answer, please mark your own reply as "correct answer", this will mark the thread as answered.

[henrique_pinto]

Since it was a syntax error, not a wrong type casting error, I hardly believe it will work with a dynamic parameter.

That being said, if you want to properly test, you need to use the datetime conversion function (TO_TIMESTAMP()) within your proc, and not in the call statement. Since the parameter is of type VARCHAR(50), this will always be the type of the param you read.

Try, in your code, something like:

SELECT DISTINCT VBAP.PSTYV AS VBAP_PSTYV, COUNT(*) AS XCOUNT FROM   
VBAP GROUP BY VBAP.PSTYV   
AS OF UTCTIMESTAMP TO_TIMESTAMP(:param);

But as I had mentioned, I hardly believe this will work...

[Former Member]

Hi Fabian,

Try to typecast datetime in timestamp as below:

CALL "sql_select_with_param"(to_timestamp('2012-07-26 12:48:56','YYYY-MM-DD HH24:MM:SS'));

Hope it will resolve your problem.

-Ruchi

[fabian_krger]

Hi Ruchi,

thanks for your hint.

I tried

CALL "sql_select_with_param" (TO_TIMESTAMP('2012-07-26 12:48:56','YYYY-MM-DD HH24:MI:SS'));

(you accidently wrote HH24:MM:SS but it has to be HH24:MI:SS)

but still no luck. Error message still the same...

[former_member184768]

Although I didn't try this personally, but can you please try converting the time to UTC format as mentioned below:

LOCALTOUTC (TO_TIMESTAMP('2012-07-26 12:48:56', 'YYYY-MM-DD HH24:MI:SS'), '

Regards,

Ravi