HSQL SELECT Statement Not working

Question

I am new in HSQL. Tried a Procedure like below;

CREATE PROCEDURE GetData(ObjectId VARCHAR(36)) READS SQL DATA 
DYNAMIC RESULT SETS 1
BEGIN ATOMIC
    DECLARE MaxVal NUMERIC(19,2);
    DECLARE MinVal NUMERIC(19,2);
    DECLARE BiggestObjectName VARCHAR(50);
    DECLARE SmallestObjectName VARCHAR(50);

    SET MaxVal = (SELECT MAX(HeightValue) FROM ObjectData WHERE ObjectId=ObjectId);
    SET MinVal = (SELECT MIN(HeightValue) FROM ObjectData WHERE ObjectId=ObjectId);
    SET BiggestObjectName = (SELECT ObjectName FROM ObjectData WHERE ObjectId=ObjectId AND HeightValue=MaxVal);
    SET SmallestObjectName = (SELECT ObjectName FROM ObjectData WHERE ObjectId=ObjectId AND HeightValue=MinVal);

  if MaxVal IS NOT NULL THEN
    DECLARE result CURSOR WITH RETURN FOR
        SELECT MaxVal AS MaximumHeight, MinVal AS MinimumHeight, BiggestObjectName AS LargestDisplayCaseName, SmallestObjectName AS SmallestDisplayCaseName FOR READ ONLY;
  OPEN result;
END

But i get error;

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ; required: INTO

Is it not the correct syntax?

Any help is appreciated :)

Answer 1

There are several things wrong with your stored procedure. For one I don't think you can declare a cursor as part of an IF statement.

Assignment of a variable needs to be either using select ... into or you need to put the select statement between parentheses:

SET MaxVal = (SELECT MAX(HeightValue) FROM ObjectData WHERE ObjectId=ObjectId);
SET ObjectName = (SELECT ObjectName FROM ObjectData WHERE ObjectId=ObjectId AND HeightValue=MaxVal);

or

SELECT MAX(HeightValue) 
  into maxval
FROM ObjectData 
WHERE ObjectId=ObjectId;

You also can't use = or <> to compare NULL values. if MaxVal != NULL THEN needs to be

if maxval is not null then 
   ...
end if; --<< you also forgot the `end if`

You also can't use a SELECT statement without a FROM clause, and I don't think you can define a cursor that only selects values from variables in HSQLDB.

But don't need the intermediate selects anyway, you can do that in a single select:

CREATE PROCEDURE GetData(ObjectId VARCHAR(36)) READS SQL DATA 
DYNAMIC RESULT SETS 1
BEGIN ATOMIC

  DECLARE result CURSOR FOR
    select o1.heightvalue as maximumheight, o1.objectname as displaycasename
    from objectdata o1
    where objectid = 'one' 
    and heightvalue = (select max(heightvalue) 
                       from objectdata o2 
                       where o2.objectid = o1.objectid);

  OPEN result;

END;