cancel
Showing results for 
Search instead for 
Did you mean: 

Current_Date Variable pass to HANA Calculation view via Stored Procedure

Former Member
0 Kudos

Hi All,

I am having trouble creating a variable in a stored procedure which will pass the current date into the Input Parameter of a calculation view.

Any help would be great.!

Thanks in advance.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PROCEDURE "MY_TEST"."TEST::TEST_QUERY" ( )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA "MY_TEST"

  AS

BEGIN

/*****************************

  Write your procedure logic

*****************************/

DECLARE var_commit  VARCHAR(100) := 'COMMIT';

DECLARE var_rollback VARCHAR(100) := 'ROLLBACK' ;

DECLARE var_cdate date:= to_date(current_date);

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

       exec (:var_rollback);

       --ex_message := 'Error Code '||::SQL_ERROR_CODE||::SQL_ERROR_MESSAGE;

END;

SELECT * FROM "_SYS_BIC"."TEST/TEST_QUERY"

('PLACEHOLDER' = ('$$P_ENDDATE$$', var_cdate),

  'PLACEHOLDER' = ('$$P_STARTDATE$$',var_cdate))

exec (:var_commit);

END;

Accepted Solutions (1)

Accepted Solutions (1)

SergioG_TX
Active Contributor

CURRENT_DATE is the current date of the system

so you can use ...

SELECT CURRENT_DATE into SOME_VAR from dummy;

in case you need to assign it to a variable..

hope this works

Former Member
0 Kudos

Hi Sergio,

Thanks for the reply. The error occuring is

Incorrect syntax near "var_enddate"

Incorrect syntax near "var_startdate"


It seems the error is when i am trying to reference the variable where the input parameter

SergioG_TX
Active Contributor
0 Kudos

:var_startdate (colon  before the variable)

Former Member
0 Kudos

Hi Sergio,

I have tried the following

('PLACEHOLDER' = ('$$P_ENDDATE$$', :var_enddate),

  'PLACEHOLDER' = ('$$P_STARTDATE$$',:var_startdate));

I still have the same

incorrect syntax near ":var_enddate"

incorrect syntax near ":var_startdate"



I have also tried to change the top section to


DECLARE var_enddate Date:= to_date('2015-01-07','YYYY-MM-DD');

DECLARE var_startate Date:= to_date('2014-01-07','YYYY-MM-DD');

still no luck

SergioG_TX
Active Contributor
0 Kudos

it may be due to the syntax on how to pass a value into the variable. in this blog 

look at the syntax

(PLACEHOLDER."$$YOUR_INPUT_VAR$$" => :VAR_DATE1, PLACEHOLDER."$$YOUR_INPUT_VAR2$$" => :VAR_DATE2)

hope this is what you need

Former Member
0 Kudos

Hi Sergio

(PLACEHOLDER."$$P_ENDDATE$$" => :var_enddate,

  PLACEHOLDER."$$P_STARTDATE$$" => :var_startdate);

Updating it to the following seems to have worked

kind regards

amish1980_95
Participant

Awesome!

Thank you for the solution , worked for me too!

So thankful!

Answers (0)