By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

problem with procedure call

P: 1
I am having problems with the following procedure call, does anyone know what I am doing wrong? Below the procedure is the error message I receive.

-- 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
Apr 14 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.