467,118 Members | 960 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

problem with procedure call

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
  • viewed: 1011
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
2 posts views Thread by Dino L. | last post: by
3 posts views Thread by Bilbo | last post: by
reply views Thread by Bruin | last post: by
30 posts views Thread by dbuchanan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.