Posted: Tue Oct 11, 2005 8:14 am Post subject: error -199 while creating procedure
When I try to execute the following create command I get error -199 "
ILLEGAL USE OF KEYWORD END Token ; WAS EXPECTED"
We are on db2 7.1 for Z/OS. I am not able to find where to put the ';'
Code:
CREATE PROCEDURE UpdateContactSupplier(
rnSupplierId int,
rsOldSupplierAddr varchar(64),
rsNewSupplierAddr varchar(64),
rsOldSupplierAddr2 varchar(64),
rsNewSupplierAddr2 varchar(64),
rsOldSupplierAddr3 varchar(64),
rsNewSupplierAddr3 varchar(64),
rsOldSupplierAddr4 varchar(64),
rsNewSupplierAddr4 varchar(64),
rsOldSupplierCity varchar(50),
rsNewSupplierCity varchar(50),
rsOldSupplierProv varchar(60),
rsNewSupplierProv varchar(60),
rsOldSupplierPCode varchar(16),
rsNewSupplierPCode varchar(16),
rsOldSupplierCountry varchar(60),
rsNewSupplierCountry varchar(60),
rnContactId int,
rsContactAddr varchar(64),
rsContactAddr2 varchar(64),
rsContactAddr3 varchar(64),
rsContactAddr4 varchar(64),
rsContactCity varchar(20),
rsContactProv varchar(60),
rsContactPCode varchar(16),
rsContactGeoLoc varchar(40))
DYNAMIC RESULT SET 0
LANGUAGE SQL
NOT DETERMINISTIC
FENCED
MODIFIES SQL DATA
P1: BEGIN
IF
(ltrim(rtrim(rsOldSupplierCity)) = rsContactCity or (rsOldSupplierCity
is null and rsContactCity is null)) AND
(ltrim(rtrim(rsOldSupplierProv)) = rsContactProv or (rsOldSupplierProv
is null and rsContactProv is null)) AND
(ltrim(rtrim(rsOldSupplierPCode)) = rsContactPCode or
(rsOldSupplierPCode is null and rsContactPCode is null)) AND
(ltrim(rtrim(rsOldSupplierCountry)) = rsContactGeoLoc or
(rsOldSupplierCountry is null and rsContactGeoLoc is null)) AND
(ltrim(rtrim(rsOldSupplierAddr)) = rsContactAddr or (rsOldSupplierAddr
is null and rsContactAddr is null)) AND
(ltrim(rtrim(rsOldSupplierAddr2)) = rsContactAddr2 or
(rsOldSupplierAddr2 is null and rsContactAddr2 is null)) AND
(ltrim(rtrim(rsOldSupplierAddr3)) = rsContactAddr3 or
(rsOldSupplierAddr3 is null and rsContactAddr3 is null)) AND
(ltrim(rtrim(rsOldSupplierAddr4)) = rsContactAddr4 or
(rsOldSupplierAddr4 is null and rsContactAddr4 is null))
THEN
UPDATE ContactsSupplier SET
CAddr = rsNewSupplierAddr,
CAddr1 = rsNewSupplierAddr2,
CAddr2 = rsNewSupplierAddr3,
CAddr3 = rsNewSupplierAddr4,
CCity = rsNewSupplierCity,
CProv = rsNewSupplierProv,
CPCode = rsNewSupplierPCode,
CGeoLoc = rsNewSupplierCountry
WHERE
ContactId = rnContactId AND SMS_Supp_Id = rnSupplierId
END IF
END P1
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Tue Oct 11, 2005 9:47 am Post subject:
Chokali,
Please do not open more than 1 topic for the same question. I deleted your duplicate post.
First couple of errors in your create statement.
DB2 does not support more than 18 characters for name. your procedure statement is more than 18 characters. Also the following column names are longer than 18 characters.
Code:
rsOldSupplierCountry
rsNewSupplierCountry
And the Second statement are you trying to create a Function with P1?
Kolusu _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
