on 04-12-2016 11:51 PM
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;
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
Awesome!
Thank you for the solution , worked for me too!
So thankful!
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.