Hi
i am getting the wrong result from the following code:
CREATE PROCEDURE DYNAMIC_QUERY ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE V_STR VARCHAR(2000);
DECLARE V_TXN_CDE VARCHAR(4);
SET V_STR='';
FOR V_CUR AS C1 CURSOR WITH HOLD FOR SELECT TXN_PFE_TXN_CDE FROM D_REF_TXN_CDE WHERE TXN_TYP_CDE='CSH'
DO
SET V_STR=V_STR||''''||V_CUR.TXN_PFE_TXN_CDE||''''||', ';
END FOR;
SET V_STR=SUBSTR(V_STR, 1, LENGTH(V_STR)-1);
IF V_TXN_CDE IN (V_STR)
THEN
INSERT INTO TEST4 VALUES (3,'CASH TRANSACTION');
ELSE
INSERT INTO TEST4 VALUES (3,'NOT CASH TRANSACTION');
END IF;
COMMIT;
END P1
IF V_TXN_CDE IN (V_STR) IS GIVING WRONG RESULT
BUT WHEN I CHECK THE VALUE OF V_STR IT IS :
'2200','2201','2202','2203','2204','2205','2206',' 2207','2208','2209','2210','2211','2212','2213','2 214','2215','2216','2260','2261','2270','2271','33 60','3361','3370','3371','4200','4300','4201','425 8'
------------------------------------
NOW WHEN I RUN THE SAME CODE BY REPLACING V_STR I GET THE CORRECT RESULT
CREATE PROCEDURE DYNAMIC_QUERY ( )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE V_STR VARCHAR(2000);
DECLARE V_TXN_CDE VARCHAR(4);
SET V_STR='';
FOR V_CUR AS C1 CURSOR WITH HOLD FOR SELECT TXN_PFE_TXN_CDE FROM D_REF_TXN_CDE WHERE TXN_TYP_CDE='CSH'
DO
SET V_STR=V_STR||''''||V_CUR.TXN_PFE_TXN_CDE||''''||', ';
END FOR;
SET V_STR=SUBSTR(V_STR, 1, LENGTH(V_STR)-1);
IF V_TXN_CDE IN ('2200','2201','2202','2203','2204','2205','2206', '2207','2208','2209','2210','2211','2212','2213',' 2214','2215','2216','2260','2261','2270','2271','3 360','3361','3370','3371','4200','4300','4201','42 58')
THEN
INSERT INTO TEST4 VALUES (3,'CASH TRANSACTION');
ELSE
INSERT INTO TEST4 VALUES (3,'NOT CASH TRANSACTION');
END IF;
COMMIT;
END P1
How to overcome this problem?
Regards
Bitul