thborges@gmail.com wrote:
[color=blue]
> Stolze,
>
> The real procedure I need build is more complex. Envolve many
> computations and verifications. I sent an example of my necessity.
>
> I see this sample in the ibm public help:
>
> CREATE FUNCTION RANK(N INTEGER)
> RETURNS TABLE(
> POSITION INTEGER,
> EMPNO CHAR(6),
> FIRSTNME CHAR(20),
> LASTNAME CHAR(20),
> SALARY DECIMAL(13,2)
> )
> LANGUAGE SQL
> DISALLOW PARALLEL
> MODIFIES SQL DATA
> NOT FENCED
> BEGIN
> DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
> DECLARE I INTEGER DEFAULT 1;
> DECLARE STMT VARCHAR(255);
> DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710'; 1
> DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS 2
> DELETE FROM SESSION.RETURN_TBL;
> DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL ( 3
> POSITION INTEGER NOT NULL,
> EMPNO CHAR(6) NOT NULL,
> FIRSTNME CHAR(20) NOT NULL,
> LASTNAME CHAR(20) NOT NULL,
> SALARY DECIMAL(13,2) NOT NULL);
>
> FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
> SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO
>
> IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN
> LEAVE FOR_LOOP;
> ELSE
> SET LAST_SALARY = EACH_ROW.SALARY;
> END IF;
>
> INSERT INTO SESSION.RETURN_TBL
> VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
> EACH_ROW.LASTNAME, EACH_ROW.SALARY);
> SET I = I + 1;
> END FOR;
>
> RETURN
> SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
> FROM SESSION.RETURN_TBL;
> END;[/color]
If I get this right, then you select some rows from the EMPLOYEE table,
insert into into a temp table and then return the stuff in the temp table.
At the same time, the parameter N limits the number of rows processed. Is
that right? If so, the following should do something along those lines:
CREATE FUNCTION rank(n INTEGER)
RETURNS TABLE (
position INTEGER,
empno CHAR(6),
firstnme CHAR(20),
lastname CHAR(20),
salary DECIMAL(13, 2) )
LANGUAGE SQL
RETURN
SELECT *
FROM ( SELECT row_number() OVER ( ORDER BY salary DESC
AS rn,
empno, firstnmo, lastname, salary
FROM sampledb01.employee ) AS t
WHERE rn < n
Ok, this is a bit different than your statement because it does not return
all the rows beyond the first N where salary is equal to the salary of the
N-th row. But this could be added if needed.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
What's incorrect with this function?
