Hi There,

Finally I wrote and compiled my stored procedure. but now i have a new problem. when i try to run it, the procedure throws Error (SQL0303 - Host variable not compatible.)

Please help


Below is the trimmed down version of my code.


P1 : BEGIN NOT ATOMIC
DECLARE CUST_NO DECIMAL(7,0);
DECLARE CUST_NAME CHAR(30);
DECLARE CUST_ITEM_NO CHAR(20);
DECLARE PACK_NO DECIMAL(5,0);
DECLARE SIZE_NO DECIMAL(7,2);
DECLARE UNITOM CHAR(2);
DECLARE LIST_PRICE DECIMAL(7,2);
DECLARE AV_MOVEMENT DECIMAL(7,0) ;
DECLARE WEEK_MOVEMENT DECIMAL(7,0) ;
DECLARE QO_HAND DECIMAL(7,0) ;
DECLARE CUSTLOC_NO DECIMAL(3,0);

DECLARE SQLSTMT CHAR(512) ;
DECLARE SQLSTMT2 CHAR (512 ) ;
DECLARE SQLSTMT3 CHAR ( 30 ) ;
DECLARE ENDTABLE2 INT DEFAULT 0 ;
DECLARE ZERO1 DECIMAL ( 3 , 0 ) ;
DECLARE ZERO2 DECIMAL ( 7 , 2 ) ;
DECLARE CUR2 CURSOR FOR S1 ;
DECLARE FINALCUR CURSOR WITH RETURN FOR S3 ;
SET ZERO1 = 0 ;
SET ZERO2 = 0 ;

SET SQLSTMT = 'SELECT A.CUST# AS CUST#, B.CNAME AS CUSTOMERNAME, A.CICITM AS
CUSTOMERITEM#, A.CIPACK AS PACK, A.CISIZE AS SIZE, A.CISUOM AS UM, A.CI$LST
AS LISTPRICE, A.CI52WM AS AVEWEEKLYMOVEMENT, A.CIQWKM AS WEEKMVM, A.CIQONH
AS QUANTITYONHAND, A.CUSTL# FROM FMGDATA.CUSTITM A, FMGDATA.CUSTLOC B WHERE
A.ISUPP# = ? AND A.IITEM# = ? AND A.CUST# = B.CUST# AND B.CUSTL# = ? AND A.CI$LST <> ? ORDER BY CUST# ';

PREPARE S1 FROM SQLSTMT ;
SET ENDTABLE2 = 1 ;
OPEN CUR2 USING SUPPN , ITEMN , ZERO1 , ZERO2 ; --suppn & itemn are IN variables.

FETCH CUR2 INTO
CUST_NO, CUST_NAME, CUST_ITEM_NO, PACK_NO , SIZE_NO, UNITOM , LIST_PRICE, AV_MOVEMENT, WEEK_MOVEMENT, QO_HAND, CUSTLOC_NO;
WHILE ENDTABLE2 = 0 DO

INSERT INTO FMGDATA.TEMP(CUST#, CUSTNAME) VALUES (CUST_NO, CUST_NAME);

FETCH CUR2 INTO CUST_NO, CUST_NAME, CUST_ITEM_NO, PACK_NO, SIZE_NO, UNITOM , LIST_PRICE , AV_MOVEMENT, WEEK_MOVEMENT, QO_HAND, CUSTLOC_NO;
END WHILE ;
CLOSE CUR2 ;
SET SQLSTMT3 = 'Select * from FMGDATA.temp ORDER BY CUST#' ;
PREPARE S3 FROM SQLSTMT3 ;
OPEN FINALCUR ;
END P1

I checked it and it seems it breaks in Fetch statement.

Keshav