Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Unanswered: Stored procedure compilation error

    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

  2. #2
    Join Date
    Jun 2004
    Posts
    28
    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 )
    Last edited by saltbits; 07-20-04 at 16:12.

  3. #3
    Join Date
    Aug 2003
    Location
    AUS
    Posts
    26
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •