Hi,

I have problem in passing parametre to cursor in an procedure.

Below is the procedure, in which I have tried to builde a string of data seperated by comma. When I pass this comma seperated string to cursor it is not returning me any data. At the same time it is not returning me any error as well.

I tried to print and see whether I am passing the correct data to the cursor. and It looked fine to me.

The same procedure will work fine if I hard code the values (Like in my procedure v_orderNumberStr = '97637' But the same will not work when I dyanmicaaly build the string and pass it to cursor.

Can anybody please tell me what is the problem in procedure.



CREATE PROCEDURE SP_GET_DEALER_SHIP_BUILDER(
IN custBase VARCHAR(7),
IN custSuffix VARCHAR(6)
)
DYNAMIC RESULT SETS 2
LANGUAGE SQL
P1: BEGIN

DECLARE v_orderNumber INTEGER DEFAULT 0;
DECLARE v_orderNumberStr VARCHAR(1040);
DECLARE v_orderNumberStr1 VARCHAR(1040);

DECLARE curDlrShipBuilder CURSOR FOR
SELECT CHAR(R1SSNG) FROM LCR1CPP WHERE CHAR(R1SSNG) in (v_orderNumberStr1) ORDER BY 1;

SET v_orderNumberStr = '97637';

BEGIN

FOR v_row AS SELECT R1SSNG FROM LCR1CPP WHERE R1WKCD='3917' AND R1WLCD='0' AND R1MCSV='00' and R1ZVCD='P' and R1ONSV='L'
DO
SET v_orderNumberStr = v_orderNumberStr || ', ' || TRIM(CHAR( v_row.R1SSNG)) ;
END FOR;
END;

SET v_orderNumberStr1 = TRIM(CHAR(v_orderNumberStr));


BEGIN
--OPEN curDlrShipBuilder1;
OPEN curDlrShipBuilder;
END;

END P1


Thanks and Regards,
Babu

That's because you are in fact passing to the IN expression a single character value, containing by coincidence something like '97637,97638,97639'. It's the difference between which is what you have, and which is what you want.

I think you'll be able to achieve your result by using a subselect directly in the IN expression:

Hi,

Thanks for your reply.

The basic idea of me joining the data (comma seperated) and sending to cursor is to improve the performance of the Query I have. Basically I have a big query which joins more than 10 tables/files. Some of these tables have huge data (more than million records). So What I did was, I broke the Query in to peices and executed them seperately. This logic Improved the peformnace drastically. So Now, Since I have 3 seperate queries, I wanted put them in procedure, where in the output each has to be merged and sent to the next query. This is the reason, I just tried with a simple query and tried to pass the comma seperated data to it.

So, I do not want to Join the Query as it is going to cause me performance problems.

If you know any other solution to my problem, it will be well appricated.

Thank you for your time
Babu







to query is to break up the data and

You might care to insert the selected values into a temporary table and then use the temp table for the join. Something along the lines of:

DECLARE curDlrShipBuilder CURSOR FOR
SELECT CHAR(R1SSNG) FROM LCR1CPP t1, session.tt t2 WHERE t1.R1SSNG = t2.r1ssng;

SET v_orderNumberStr = '97637';

declare global temprary table tt as (SELECT R1SSNG FROM LCR1CPP) definition only;

insert into session.tt values(97637);

insert into session.tt SELECT R1SSNG FROM LCR1CPP WHERE R1WKCD='3917' AND R1WLCD='0' AND R1MCSV='00' and R1ZVCD='P' and R1ONSV='L' ;

--OPEN curDlrShipBuilder1;
OPEN curDlrShipBuilder;


James Campbell

Hi James,

Thankyou for your reply, The logic works, but the if you try to execute the same procedure many times in the same session, it says the table already exists. So I used 'WITH REPLACE' when creating the remporary table.

With this, I have one more problem. I am passing a comma seperated value to the procedure. Again I have the same problem, it is treating it as a Single string.

For EX, '0','0001','0002' This is the string I am passing to the procedure and I want to use it in the query with in the IN clause.

Any Ideas,

Thanks again for your logic.
Cheers,
Babu

You'll have to use EXECUTE IMMEDIATE then.