hi friends,

while compiling the below stored procedure, i get compilation error.
--------------------------------------------------------------------------
CREATE FUNCTION FN_AVG_SAL_SCALAR
(
P_DEPTNO VARCHAR(6)
)
RETURNS DECIMAL(18, 2)
SPECIFIC AVERAGE_SALARY
BEGIN ATOMIC
DECLARE v_avg_salary DECIMAL(9,2);
DECLARE v_total_salary DECIMAL(18,2) DEFAULT 0;
DECLARE v_total_employees INT;

SELECT COUNT(EMPNO) INTO v_total_employees FROM EMPLOYEE;

FOR emp AS
SELECT salary FROM employee WHERE WORKDEPT = P_DEPTNO;
DO
SET v_total_salary = v_total_salary + salary;
END FOR;

SET v_avg_salary = v_total_salary/v_total_employees;

RETURN v_avg_salary;
END
@

--------------------------------------------------------------------------

compilation error:
--------------------------------------------------------------------------
C:\products>db2 -td@ -f FUNC_AVG_SALARY_SCALAR_FUNCTION.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 "v_total_employees" was found following "CT
COUNT(EMPNO) INTO". Expected tokens may include: "<space>". LINE NUMBER=12.
SQLSTATE=42601
--------------------------------------------------------------------------

can anyone tell me why it occurs....

DB Details:
DB2 8.1 on windows
using sample database bundled with the udb.
Employee table:
--------------------------------------------------------------------------
CREATE TABLE EMPLOYEE (
EMPNO CHARACTER(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHARACTER(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHARACTER(3),
PHONENO CHARACTER(4),
HIREDATE DATE,
JOB CHARACTER(8),
EDLEVEL SMALLINT NOT NULL,
SEX CHARACTER(1),
BIRTHDATE DATE,
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMM DECIMAL(9,2)
)
@

INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000010','CHRISTINE','I','HAAS','A00','3978','196 5-01-01','PRES ',18,'F','1933-08-24',52750.00,1000.00,4220.00)@
INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000020','MICHAEL','L','THOMPSON','B01','3476','1 973-10-10','MANAGER ',18,'M','1948-02-02',41250.00,800.00,3300.00)@
INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000030','SALLY','A','KWAN','C01','4738','1975-04-05','MANAGER ',20,'F','1941-05-11',38250.00,800.00,3060.00)@
INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000050','JOHN','B','GEYER','E01','6789','1949-08-17','MANAGER ',16,'M','1925-09-15',40175.00,800.00,3214.00)@
INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000060','IRVING','F','STERN','D11','6423','1973-09-14','MANAGER ',16,'M','1945-07-07',32250.00,500.00,2580.00)@
INSERT INTO EMPLOYEE (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO, HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,CO MM) VALUES ('000070','EVA','D','PULASKI','D21','7831','1980-09-30','MANAGER ',16,'F','1953-05-26',36170.00,700.00,2893.00)@

--------------------------------------------------------------------------
Jake

u mention stored procedure but ur code creates a scalar function...
you might want to try something like this:

CREATE FUNCTION FN_AVG_SAL_SCALAR (P_DEPTNO VARCHAR(6))
RETURNS DECIMAL(18, 2)
RETURN
with emp (v_total_salary) as
(
SELECT SUM(SALARY) FROM EMPLOYEE WHERE WORKDEPT = P_DEPTNO
)

select (v_total_salary/(SELECT COUNT(EMPNO) FROM EMPLOYEE)) from emp

(although the logic in the function beats me )

To use a SELECT INTO statement you might need the following privilege
SELECT privilege
CONTROL privilege
SYSADM or DBADM authority

refer this documentation page

https://aurora.vcu.edu/db2help/db2s0...3.htm#sqls0654

might help you.

cheers
Melb