I tried like below;
CREATE PROCEDURE GetHs(IN FRVDate TIMESTAMP, IN TRVDate TIMESTAMP, IN RESValue NUMERIC(19,2),
RangeType VARCHAR(20), HRID VARCHAR(36))
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN ATOMIC
DECLARE TABLE myList(MValue NUMERIC(19,2), Price NUMERIC(19,2), FRVDate TIMESTAMP,TRVDate TIMESTAMP);
DECLARE Counter INT;
DECLARE dateDifference NUMERIC(19,2);
DECLARE for_day,for_week VARCHAR(20);
SET for_day = 'DAY';
SET for_week = 'WEEK';
IF RangeType = for_day THEN
BEGIN ATOMIC
----statements----
END
ELSEIF RangeType = for_week THEN
BEGIN ATOMIC
----statements----
END
END IF
SELECT MValue,Price,FRVDate,TRVDate FROM myList
END
I get error that user lacks privilege or object not found: DAY
. Can i not set values to Declared scalar variables?
In Documentation it says to use in this format. From DOC;
BEGIN ATOMIC
DECLARE temp_zero DATE;
DECLARE temp_one, temp_two INTEGER DEFAULT 2;
DECLARE temp_three VARCHAR(20) DEFAULT 'no name';
-- more statements ...
SET temp_zero = DATE '2010-03-18';
SET temp_two = 5;
-- more statements ...
END
Whats i am doing wrong here?
NOTE: Started Learning HSQL Recently and Using version 2.3.3
This issue has been fixed in the latest HSQLDB code. The next version 2.4.0 supports it. A snapshot of the next version is currently available at http://hsqldb.org/support