MySQL Forums
Forum List  »  IBM DB2

problem with procedure call
Posted by: jay patel
Date: April 13, 2006 06:13PM

I am having problems with the following procedure, does anyone know what I am doing wrong? Below the procedure is the error message I get.

-- Stored procedure to do a non-clustered index scan
CREATE PROCEDURE IDX (IN selectivity REAL,
IN seed INTEGER,
IN vmin INTEGER,
IN vmax INTEGER,
IN unit INTEGER)
LANGUAGE SQL
BEGIN
DECLARE hi INTEGER;
DECLARE lo INTEGER;
DECLARE id INTEGER;
DECLARE val REAL;

SET id = unit;
WHILE id > 0 DO
SET lo = INTEGER(vmin + RAND(seed) * (vmax - vmin) / (1 + selectivity));
SET hi = INTEGER(lo + (vmax - vmin) * selectivity);

SELECT AVG(L_LINENUMBER)
FROM LINEITEM
WHERE L_PARTKEY > lo AND L_PARTKEY <= hi;

SET id = id - 1;
END WHILE;

END
@



C:\mbenchkit\mbench_tpcc>db2 -td@ -f idx.sql
DB21034E The command was processed as an SQL statement because it was not a valid
Command Line Processor command. During SQL processing it returned: SQL0104N An
unexpected token "SELECT AVG(L_LINENUMBER) FROM LI" was found following ") *
selectivity); ". Expected tokens may include:"<psm_return>". LINE NUMBER=19.
SQLSTATE=42601



Edited 1 time(s). Last edit at 04/13/2006 06:39PM by jay patel.

Options: ReplyQuote


Subject
Views
Written By
Posted
problem with procedure call
5400
April 13, 2006 06:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.