469,271 Members | 1,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Problems when run one procedure in DB2

Hi to all !!! And thanks to answer my topics.

I have problems when I try of run stored procedures, show diferents
errors. I made one test with one example of the tutorials. The example
is this

CREATE PROCEDURE RETURN_DEPT_SALARY
(IN DEPT_NUMBER CHAR(3),
OUT DEPT_SALARY DECIMAL(15,2),
OUT DEPT_BONUS_CNT INT)
LANGUAGE SQL READS SQL DATA
P1: BEGIN
DECLARE EMPLOYEE_SALARY DECIMAL(9,2);
DECLARE EMPLOYEE_BONUS DECIMAL(9,2);
DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0
DECLARE BONUS_CNT INT DEFAULT 0;
DECLARE END_TABLE INT DEFAULT 0;
DECLARE C1 CURSOR FOR
SELECT SALARY, BONUS FROM RJOHNSON.EMPLOYEE
WHERE WORKDEPT = DEPT_NUMBER;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET DEPT_SALARY = NULL;
OPEN C1;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
WHILE END_TABLE = 0 DO
SET TOTAL_SALARY = TOTAL_SALARY + EMPLOYEE_SALARY +
EMPLOYEE_BONUS;
IF EMPLOYEE_BONUS 0 THEN
SET BONUS_CNT = BONUS_CNT + 1;
END IF;
FETCH C1 INTO EMPLOYEE_SALARY, EMPLOYEE_BONUS;
END WHILE;
CLOSE C1;
SET DEPT_SALARY = TOTAL_SALARY;
SET DEPT_BONUS_CNT = BONUS_CNT;
END P1;

And show between others message like these

DECLARE EMPLOYEE_SALARY DECIMAL(9,2)
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 "END-OF-STATEMENT" was found following
"_SALARY
DECIMAL(9,2)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=7. SQLSTATE=42601

DECLARE EMPLOYEE_BONUS DECIMAL(9,2)
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 "DECLARE EMPLOYEE_BONUS DECIMAL" was
found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>".
SQLSTATE=42601

If I change the ";" for "," show others messages

I made stored procedure but show same messages.

Somebody know what is the problem?

Of new thanks to all for answer

Iván

Aug 28 '06 #1
4 7123

Ivan wrote:
Hi to all !!! And thanks to answer my topics.

I have problems when I try of run stored procedures, show diferents
errors. I made one test with one example of the tutorials. The example
is this
END P1;
hi,

remove the semicolon at the last

1) u have specified "END P1;" change it to "END P1"

2) look at this DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0

put semicolon for this at last like

DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0;

Aug 28 '06 #2
I made the change indicates but the problem is the same.

Which semicolon I should removed?

Thanks

Iván.
DECLARE EMPLOYEE_SALARY DECIMAL(9,2)
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 "END-OF-STATEMENT" was found following
"_SALARY
DECIMAL(9,2)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=7. SQLSTATE=42601

DECLARE EMPLOYEE_BONUS DECIMAL(9,2)
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 "DECLARE EMPLOYEE_BONUS DECIMAL" was
found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>".
SQLSTATE=42601

situ wrote:
Ivan wrote:
Hi to all !!! And thanks to answer my topics.

I have problems when I try of run stored procedures, show diferents
errors. I made one test with one example of the tutorials. The example
is this
END P1;

hi,

remove the semicolon at the last

1) u have specified "END P1;" change it to "END P1"

2) look at this DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0

put semicolon for this at last like

DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0;
Aug 28 '06 #3
If you want to go this route of statement termination, remove the very
last semicolon (after the final "END"). Do put a smeicolon after
"DECLARE EMPLOYEE_SALARY DECIMAL(9,2)," however.

--Jeff

Ivan wrote:
I made the change indicates but the problem is the same.

Which semicolon I should removed?

Thanks

Iván.
DECLARE EMPLOYEE_SALARY DECIMAL(9,2)
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 "END-OF-STATEMENT" was found following
"_SALARY
DECIMAL(9,2)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=7. SQLSTATE=42601

DECLARE EMPLOYEE_BONUS DECIMAL(9,2)
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 "DECLARE EMPLOYEE_BONUS DECIMAL" was
found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>".
SQLSTATE=42601

situ wrote:
Ivan wrote:
Hi to all !!! And thanks to answer my topics.
>
I have problems when I try of run stored procedures, show diferents
errors. I made one test with one example of the tutorials. The example
is this
>
END P1;
>
hi,

remove the semicolon at the last

1) u have specified "END P1;" change it to "END P1"

2) look at this DECLARE TOTAL_SALARY DECIMAL(15,2)DEFAULT 0

put semicolon for this at last like

DECLARE TOTAL_SALARY DECIMAL(15,2) DEFAULT 0;
Aug 28 '06 #4
jefftyzzer wrote:
If you want to go this route of statement termination, remove the very
last semicolon (after the final "END"). Do put a smeicolon after
"DECLARE EMPLOYEE_SALARY DECIMAL(9,2)," however.
Something else to check is that you don't have Windows end-of-line and
carriage-return characters in your SQL script. Otherwise, DB2 won't accept
the statement terminator as such.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 28 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Henry Reardon | last post: by
9 posts views Thread by serge | last post: by
1 post views Thread by Chad Dittmer via .NET 247 | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.